473,574 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

"Transactio n over connections"

Hi !

We are moving to PgSql application, with 'typical' style of work. Thin
client, huge database. From database is (also) generated whole website. I
will use it for explain my current todo. Right now, I am before solving
following problem:

Someone starts (let's say) adding 'new product'. It mean, he have to add
records to many tables, gfxer have to create proper images and some XML/HTML
code, ppl translating to other languages have to translate, etc.... Before
the new product is finished and later accepted, user "www" (the one
generating public web) have to 'ignore' all these new records (ofcourse,
many foreign keys, stored procedures are used). When new products will be
tested (on web :) and everything fixed, all records in all tables related to
this product should become visible to all. Basically, something like
"database transaction" which can be re-used accross various DB connections
and 'commit' at some point. I saw some ppl solving this (typical) problem
with local copy of DB and 'udpate', but I don't like that... It is possible
to do 'export' at local copy and 'import' on server. But....

I am asking for your thoughts how to do this with PgSql - the best for
performance. I am sure, many ppl had similar thing to do, but I didn't find
any experimence results. If there are some articles around, I would be happy
for links.

So, I think we need to add 'system' colum to EVERY table in the system (for
performance reasons) - something like

transaction_id

(or something like that, details not important, it is about general 'how
to')

One of possible ways (I don't think the best) is to improve all SQL's, to
reflect these columns. Something, what will do simple test - like
"transaction_id =0 OR transaction_id= $attached$"...

So, my direct question: can I invoke automatically some function in PgSql to
make condition to EVERY sql statement ? Or what way will be the best for
performace ? Does PgSql allready include some support for this thing ? I
have done something for Oracle, but it will not work at PgSql world.I am
novice to PgSql at all.

The whole process need to be 'transparent' to all part of system, except
parts, where these 'transactions' will be managed - like "create",
"roolback" and "commit". My imagination is something like:

Admin will create global transaction - e.g. "New product"

Users having proper access rigth can 'connect' to this transaction and
EVERYTHING what they will do, will be visible only 'inside' this
transaction. All users, NOT connected will not see any records - it means,
all SQL will not returns related rows. Next required operation is to connect
to MORE transactions - READ ONLY access (write is not possible ofcourse
then)... One day, transaction and all records can be rollback/commin -
together by 'id', or move to another transaction (glued). Thats' all :)

Thank you for opinions/ideas/hints,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
3 2155
> So, my direct question: can I invoke automatically some function in PgSql to
make condition to EVERY sql statement ?

You can add an attribute "work-in-progress" to your tables. Then
set up views that filter out rows with work-in-progress=true.
Your production client then needs to be modified to look at
those views instead of the tables.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2
> You can add an attribute "work-in-progress" to your tables. Then
set up views that filter out rows with work-in-progress=true.
Your production client then needs to be modified to look at
those views instead of the tables.

I also though about this, but what about performance ? I have hundreds
tables. Anyone have experimence withs TONS of views ?

Is it possible to setup 'view' on whole database ? Or a 'part' of DB ? Same
expression for all/setof tables.

It is not problem to modify SQL statement 'table' name (it is in reality
easier than modify expression :). So, client doesn't need to be changed -
just 'SQL' exec handler... So, base condition is OK.

Thanks,
R.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #3
Lada 'Ray' Lostak wrote:
You can add an attribute "work-in-progress" to your tables. Then
set up views that filter out rows with work-in-progress=true.
Your production client then needs to be modified to look at
those views instead of the tables.


I also though about this, but what about performance ? I have hundreds
tables. Anyone have experimence withs TONS of views ?

Is it possible to setup 'view' on whole database ? Or a 'part' of DB ? Same
expression for all/setof tables.


how about all non-approved product goes to a in development schema and all
approved products are moved to production schema when approved.

Sure it means lot more than mainening single column but development schema would
be as slim as productio and further more it is totally invisible from client side.

Just set the schema search pats correctly.

HTH..

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4457
by: Frank | last post by:
I have observed that a temporary loss of a domain controller can cause problems creating new ado connections between a client machine running ado and a separate sql server machine that are members of the domain. I understand why this happens when creating connections with windows authentication. What is a mystery is that it also sometimes...
3
427
by: Lada 'Ray' Lostak | last post by:
Hi ! We are moving to PgSql application, with 'typical' style of work. Thin client, huge database. From database is (also) generated whole website. I will use it for explain my current todo. Right now, I am before solving following problem: Someone starts (let's say) adding 'new product'. It mean, he have to add records to many tables,...
1
1724
by: Jean-Marc Blaise | last post by:
Dear all, It seems to me the IY54968 apar (V8.2) does not correct totally the problem: db2start db2 connect to sample db2 get snapshot for db on sample | grep "oldest transaction" ==> What should be the result ? If I am not mistaken, it should be 0, as of version 7, as a "connect
3
27492
by: Ollie Riches | last post by:
"Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." I am recieving the above error when attempt to access a remote sql server database 2000 (sp3a) from ADO.Net. The database is on a windows 2003 machine and the ado.net code (web services) are on another winsdows 2003 machine in the...
1
2233
by: Axel Dahmen | last post by:
Hi, today I've had a strange DTS error: In one of my transformations I'd been using several Copy Column transformations + one ActiveX transformation using a lookup function. This lookup function uses a second connection to the same SQL server but to a different database. I'm using this function to populate a new column in a table with...
2
9484
by: Gregory S. Williamson | last post by:
Dear peoples, Periodically we are getting runaway postgres processes on our Linux (2.4.21-0.13 on Dell servers), using 7.4 and GIS (0.8 USE_GEOS=1 USE_PROJ=1 USE_STATS=1). All of the queries come in from remote servers using JDBC/proxool; once every 4 hours we have a process on the client side that cleans out old connections. All the...
1
1419
by: optimistck | last post by:
A mistake was made and "old" transaction logs (and old backups) were deleted. It appears that a long living transaction was living in one of those logs. Now the EP shows (no items). The DBs themselves are still functioning OK, just the EP shell that's not working. What's the proper resolution? Thank you in advance, FBCK
2
17850
by: Martin Z | last post by:
I'm using the TableAdapterHelper to set the connection and transaction properties on all the commands of all my typed table adapters.... I've checked at the time of the error and all the commands have their Transaction property set. But I still get this error when I call... documentTA.Update(documentDS.Document); "ExecuteReader requires...
5
3539
by: Cirene | last post by:
I just deployed my new ASP.NET (3.5 FW) site to the hosting company I'm using, webhost4life. NOTE: I HAVE deployed other SQL Server sites to the same account with no issues. Now I'm getting this error. Any idea why? Server Error in '/myuser4/MyWebApp' Application....
0
7807
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8241
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8100
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6456
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5624
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3742
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1344
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1061
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.