Re: [SQL] filtering after join
I can see the final plan by using the EXPLAIN command. But I want to know the procedure of the planner in handling nesting queries. Can you direct me to the corresponding part of the code and/or the documents? Thanks. On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > andrew wrote: > > How will the query planner do for a nesting query? Treat the > > subqueries as multiple queries and then link them together? > > where can I find the information (codes or documents)? > > Look at the execution plan using the EXPLAIN command. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] filtering after join
I got errors in this query. I have a function complete(record) which takes a generic record type data. But it seems cannot be applied to a sub-select result: backend> explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) QUERY: explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) ERROR: Cannot pass result of sub-select or join s to a function On 1/27/06, andrew <[EMAIL PROTECTED]> wrote: > I can see the final plan by using the EXPLAIN command. But I want to > know the procedure of the planner in handling nesting queries. Can you > direct me to the corresponding part of the code and/or the documents? > Thanks. > > On 1/26/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > andrew wrote: > > > How will the query planner do for a nesting query? Treat the > > > subqueries as multiple queries and then link them together? > > > where can I find the information (codes or documents)? > > > > Look at the execution plan using the EXPLAIN command. > > > > -- > > Peter Eisentraut > > http://developer.postgresql.org/~petere/ > > > > > -- > andrew > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Changing the transaction isolation level within
On Thu, 2006-01-26 at 10:42 +0100, Markus Schaber wrote: > >>>Is it possible to change the transaction level within the procedure? > >>No, currently not, the PostgreSQL "stored procedures" really are "stored > >>functions" that are called inside a query, and thus cannot contain inner > >>transactions. > > Is above true for the newly introduced stored procedures? (Above, when > > mentioning 'stored procedures' I was actualy reffering to 'functions'). > > I have to admit that I don't know what "newly introduced stored > procedures" you're talking about? Is this an 8.2 feature? And I have to cry 'Why am I not Dumbo, so I could cover myslef with me ears'. I was reffering to the IN/OUT parametar ability for the functions, and confused them with 'stored procedures' in other RDBMSes. > PostgreSQL (and e. G. Oracle) use a MVCC system for transactions, that > doesn't need exclusive locks. Has nothing to do with a thread, but, even with MVCC you sometimes need locking. One case is tracking stock by FIFO metod. There, you need to serialize transactions which take items from the stock, so they're ordered. > I suggest you to read "Chapter 12. Concurrency Control" from the > PostgreSLQ docs. > > Its easy: if you need "read committed" guarantees, then run the entire > transaction as "read committed". If you need "serializable", then run > the entire transaction as "serializable". If you need real serialization > and synchronization of external programs, use LOCK (or take a deep > breath, redesign your application and use e. G. LISTEN/NOTIFY. Most > times, the usage of LOCK is a good indicator of misdesign.) But, I need to control those from the client, right? For some action that I'd like database to perform I created functions as an interface to the application. I'd like for database to take care about transactions and everything. So, there is one 'external' function that client application calls, and dozen of 'internal' functions that should be never called by the application (those reside in shema not visible by the client application role - so the app developers can't easily get to them). > > I just re-read your original posting. You want to make thread B wait > until thread A has committed. This will not be possible with the ACID > levels. Even when using "serializable" for both threads. If thread B > issues SELECT after thread A committed, then all works fine. If thread B > issues SELECT before thread A commits, it sees the database in the state > it was before thread A started its transaction (so even create_message > has not been called). It cannot know whether thread A will COMMIT or > ROLLBACK. Well, what happens now is that thread A calls set_message_status, and sets the status for the message to, let's say, 20. Then the thread continues to work, and if everything goes well, does COMMIT at the end. But, sometimes, if the calculations after the set_message_status take longer (actually if the SMS gateway goes bezerk, the reconnection timeout causes thread A to execute a bit longer) thread B starts doing something for that message, and it needs to set the message status to 90. So it calls set_message_status, and commits. But, when thread A commits, the status is back to 20. So I tought I'd do a rowlock while doing an UPDATE on table messages (that's what set_message_status does) so that thread B needs to wait untill thread A commits or rollbacks. If it commits, thread B woud do the update, if it rollback, thread B would return '0 rows updated' (because if thread A issues rollback at the end, the create_message is also rolled back, so there is nothing to update for thread B.). I tought serialization woud help here. Part of my problem goes from working with MSSQL for too long :) There you can have nested transactions, and you can have different isolation levels for them. But, apparently, there was an error in process design. Thread B is fired up from within the calculations in thread A. But, thread B should be started only and only if thread A commits. > > Transaction isolation is about consistency guarantees, not for true > serialization. The reason for this is that databases with high load will > need to allow paralellism. > > So for your case, threas A should issue "NOTIFY" before COMMIT, and then > thread B should use LISTEN and then wait for the notification before > beginning its transaction. Be shure to read the paragraph about how > "NOTIFY interacts with SQL transactions" in the NOTIFY documentation. I've been playing with that, but performance drops significantly with those notifications. And, it would be a hack. We redesigned the process so it makes sense :) > > I don't know the exact sematics of set_message_status and your checks, > but it may be another solution to split thread A into two transactions > by committing after step 3, and another BEGIN after step 4. Yes, that was a guideline :) Thank you for your effort! Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I
[SQL] REPOST:Memory Allocation error using pg_dump on 7.4
I repeatedly get this error whenever I try to backup a database The command used is: pg_dump -Fc -O -U username tablename > tablename.20060122 pg_dump: ERROR: invalid memory alloc request size 4294967290 pg_dump: SQL command to dump the contents of table "cc_ratecard" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967290 pg_dump: The command was: COPY public.tablename(id, ... Is there a bug somewhere in there? Is there something which needs doing in regard to my memory allocation settings? The table involved is one of the larger tables, but at only 400,000 records it shouldn't be a problem This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] How to find a temporary table
Hi, I am using PostgreSQL 8.1.0 . How can I find a temp table from my session, having the name of the table? Can anyone show me what query should I execute? I've tried some things but I receive mixed results of tables from different sessions, which is strange. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to find a temporary table
On Fri, 2006-01-27 at 04:48 -0800, Emil Rachovsky wrote: > Hi, > I am using PostgreSQL 8.1.0 . How can I find a temp > table from my session, having the name of the table? > Can anyone show me what query should I execute? I've > tried some things but I receive mixed results of > tables from different sessions, which is strange. What do you mean by 'temp table from your session'? How did you create the temp table in the first place? Mario ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] filtering after join
andrew <[EMAIL PROTECTED]> writes: > I got errors in this query. I have a function complete(record) which > takes a generic record type data. But it seems cannot be applied to a > sub-select result: As I seem to recall having mentioned several times already, PG 7.3 is really, really weak in this area. (If you ever got it to work, which I doubt you will, you'd probably then start to notice how badly it leaks memory for whole-row operations...) You should either forget this idea or invest the effort to move up to PG 8.1. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] filtering after join
Sorry. I am working on a research prototype built on 7.3 which contains a cosiderable amount of efforts. The original developers of the prototype have no interest in upgrading it. It is also impossible for me to upgrade it by myself. Could you give me any tips to sovle the current problem? Thanks! On 1/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
I have traced the code. It exits when the argument is the result of a join or a subselect in function ParseFuncOrColumn(). The reason mentioned in the comments is lack of named tuple type. How can force it to create such a tuple type? is there a way? thanks a million times! On 1/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Question about check constraints
Hi all, I have a table where two columns have two different check constraints associated with them. When I update one column, the check constraint on the other column is also executed. Is there a way to avoid this? I want to check only for the condition defined for the column being updated. Thanks, Kashmira
Re: [SQL] Question about check constraints
On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) wrote: > I have a table where two columns have two different check constraints > associated with them. When I update one column, the check constraint on > the other column is also executed. Is there a way to avoid this? I want > to check only for the condition defined for the column being updated. I don't think you can change this behavior: each CHECK constraint is evaluated for the new row regardless of whether a particular column changed or not. However, you could enforce the constraints with a trigger and skip checks where NEW.column is the same as OLD.column. Why the concern? Are the checks expensive? Do they have side effects? What do they do? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question about check constraints
Both concerns. 1) There are actually more than two columns with such checks, and each one calls a few functions which execute some more queries. So I would like to invoke these checks only when necessary. 2) The bigger concern is the side effect: Here's my schema: CREATE TABLE vm_device ( device_id INTEGER UNIQUE NOT NULL REFERENCES device_table(device_id) ON UPDATE CASCADE ON DELETE CASCADE, preference VARCHAR(1) NOT NULL DEFAULT 'U' CHECK (CASE WHEN preference = 'U' THEN true ELSE validate_preference() END), enabledBOOLEAN NOT NULL DEFAULT false CHECK (CASE WHEN enabled = false THEN true ELSE validate_system_enabled() AND validate_enabled(device_id) END), attach_vm BOOLEAN NOT NULL DEFAULT false CHECK (CASE WHEN attach_vm = false THEN true ELSE validate_attach_vm() END), PRIMARY KEY (device_id) ) WITHOUT OIDS; This table contains some information about a device in my system. The issue is with the enabled column. It basically enables/disables the device. The device can be enabled only when the two check conditions pass. But once it is enabled, the conditions of the system might change such that if executed again, these conditions might not pass. We want to allow such situations. The problem arises when we want to change the value of some other column, say attach_vm. Although the check constraints for the attach_vm column pass, those for enabled column fail, and I cannot complete my updates. Any suggestions on the best way to overcome this? Thanks, kashmira -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 4:40 PM To: Kashmira Patel (kupatel) Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Question about check constraints On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) wrote: > I have a table where two columns have two different check > constraints associated with them. When I update one column, the check > constraint on the other column is also executed. Is there a way to > avoid this? I want to check only for the condition defined for the column being updated. I don't think you can change this behavior: each CHECK constraint is evaluated for the new row regardless of whether a particular column changed or not. However, you could enforce the constraints with a trigger and skip checks where NEW.column is the same as OLD.column. Why the concern? Are the checks expensive? Do they have side effects? What do they do? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Question about check constraints
On Fri, 27 Jan 2006, Kashmira Patel (kupatel) wrote: > Both concerns. > 1) There are actually more than two columns with such checks, and each > one calls a few functions which execute some more queries. So I would > like to invoke these checks only when necessary. > 2) The bigger concern is the side effect: Here's my schema: > > CREATE TABLE vm_device > ( > device_id INTEGER UNIQUE NOT NULL > REFERENCES device_table(device_id) > ON UPDATE CASCADE > ON DELETE CASCADE, > preference VARCHAR(1) NOT NULL DEFAULT 'U' > CHECK (CASE WHEN preference = 'U' > THEN true > ELSE validate_preference() >END), > enabledBOOLEAN NOT NULL DEFAULT false > CHECK (CASE WHEN enabled = false > THEN true > ELSE > validate_system_enabled() AND > > validate_enabled(device_id) >END), > attach_vm BOOLEAN NOT NULL DEFAULT false > CHECK (CASE WHEN attach_vm = false > THEN true > ELSE validate_attach_vm() >END), > > PRIMARY KEY (device_id) > ) WITHOUT OIDS; > > This table contains some information about a device in my system. The > issue is with the enabled column. It basically enables/disables the > device. The device can be enabled only when the two check conditions > pass. But once it is enabled, the conditions of the system might change > such that if executed again, these conditions might not pass. We want to > allow such situations. The problem arises when we want to change the > value of some other column, say attach_vm. Although the check > constraints for the attach_vm column pass, those for enabled column > fail, and I cannot complete my updates. In that case check constraints are probably not the correct tool. IIRC the theoretical model from the spec implies that all check constraints in the system are supposed to be satisfied at their check time (statement end in most cases) and that it doesn't matter what the change was, and as such, the conditions of the system shouldn't have been allowed to change such that a row with enabled=true existed when its constraint would be violated at this moment. I don't think that's practically reasonable to enforce in general, but we do the best we can which is fail the later update. > Any suggestions on the best way to overcome this? I'd second Michael's suggestion of a trigger. > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Friday, January 27, 2006 4:40 PM > To: Kashmira Patel (kupatel) > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Question about check constraints > > On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) > wrote: > > I have a table where two columns have two different check > > constraints associated with them. When I update one column, the check > > constraint on the other column is also executed. Is there a way to > > avoid this? I want to check only for the condition defined for the > column being updated. > > I don't think you can change this behavior: each CHECK constraint is > evaluated for the new row regardless of whether a particular column > changed or not. However, you could enforce the constraints with a > trigger and skip checks where NEW.column is the same as OLD.column. > > Why the concern? Are the checks expensive? Do they have side effects? > What do they do? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about check constraints
Thanks, Michael and Stephan, for the replies. I think I will change my schema and remove the check constraint on the enable column, since, as Stephan pointed out, it was bad/wrong design to begin with. Thanks again. Kashmira -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 9:25 PM To: Kashmira Patel (kupatel) Cc: Michael Fuhr; pgsql-sql@postgresql.org Subject: Re: [SQL] Question about check constraints On Fri, 27 Jan 2006, Kashmira Patel (kupatel) wrote: > Both concerns. > 1) There are actually more than two columns with such checks, and each > one calls a few functions which execute some more queries. So I would > like to invoke these checks only when necessary. > 2) The bigger concern is the side effect: Here's my schema: > > CREATE TABLE vm_device > ( > device_id INTEGER UNIQUE NOT NULL > REFERENCES device_table(device_id) > ON UPDATE CASCADE > ON DELETE CASCADE, > preference VARCHAR(1) NOT NULL DEFAULT 'U' > CHECK (CASE WHEN preference = 'U' > THEN true > ELSE validate_preference() >END), > enabledBOOLEAN NOT NULL DEFAULT false > CHECK (CASE WHEN enabled = false > THEN true > ELSE > validate_system_enabled() AND > > validate_enabled(device_id) >END), > attach_vm BOOLEAN NOT NULL DEFAULT false > CHECK (CASE WHEN attach_vm = false > THEN true > ELSE validate_attach_vm() >END), > > PRIMARY KEY (device_id) > ) WITHOUT OIDS; > > This table contains some information about a device in my system. The > issue is with the enabled column. It basically enables/disables the > device. The device can be enabled only when the two check conditions > pass. But once it is enabled, the conditions of the system might > change such that if executed again, these conditions might not pass. > We want to allow such situations. The problem arises when we want to > change the value of some other column, say attach_vm. Although the > check constraints for the attach_vm column pass, those for enabled > column fail, and I cannot complete my updates. In that case check constraints are probably not the correct tool. IIRC the theoretical model from the spec implies that all check constraints in the system are supposed to be satisfied at their check time (statement end in most cases) and that it doesn't matter what the change was, and as such, the conditions of the system shouldn't have been allowed to change such that a row with enabled=true existed when its constraint would be violated at this moment. I don't think that's practically reasonable to enforce in general, but we do the best we can which is fail the later update. > Any suggestions on the best way to overcome this? I'd second Michael's suggestion of a trigger. > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Friday, January 27, 2006 4:40 PM > To: Kashmira Patel (kupatel) > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Question about check constraints > > On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) > wrote: > > I have a table where two columns have two different check > > constraints associated with them. When I update one column, the > > check constraint on the other column is also executed. Is there a > > way to avoid this? I want to check only for the condition defined > > for the > column being updated. > > I don't think you can change this behavior: each CHECK constraint is > evaluated for the new row regardless of whether a particular column > changed or not. However, you could enforce the constraints with a > trigger and skip checks where NEW.column is the same as OLD.column. > > Why the concern? Are the checks expensive? Do they have side effects? > What do they do? ---(end of broadcast)--- TIP 6: explain analyze is your friend
