[HACKERS] pg_proc.proacl documentation request
All, I am looking for documentation on the pg_proc.proacl column. I am working on a schemadiff and when I expect the acl to be the same, they come out slightly different. I searched the PG 7.4 doc site, and didn't find anything except the one liner on the pg_proc description page. Any kind of documentation, other than the code itself, would be most welcome. :D Jonathan Scott ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Hello, I have discovered a problem with psql 7.3.4 where it does not seem to like statements containing NATURAL CROSS JOIN. I have a test that I have created that will show the problem. Please have a look at it, give it a try, and let me know if there is a problem with the program or with the operator. ;) Just in case, I am running PG 7.3.4 on a SuSE 9.0 x86 box. Thanks, Jonathan Scott -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 psql.7.3.4.cross.bug.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Tom, I have included a pg_dump of the schema that causes this problem. If you take out the word CROSS from my source files, it should load just fine. If you then pg_dump it, in there you should find CROSS. Jonathan Scott On Thu, 06 May 2004 22:26:13 -0400 Tom Lane [EMAIL PROTECTED] wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql grammar does not appear to allow it. Hm. The syntax NATURAL CROSS JOIN is specifically disallowed by SQL99 and our parser (see attached SQL99 excerpt). If pg_dump produces that in a view dump then that's a bug, but this test case doesn't let me see it happen, because the parser rejects the given view definition. Do you happen to have the original input that created the view? regards, tom lane joined table ::= cross join | qualified join | natural join | union join cross join ::= table reference CROSS JOIN table primary qualified join ::= table reference [ join type ] JOIN table reference join specification natural join ::= table reference NATURAL [ join type ] JOIN table primary ... join type ::= INNER | outer join type [ OUTER ] outer join type ::= LEFT | RIGHT | FULL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 test_dump.sql Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Check Constraints and pg_dump
Tom, I have another instance of a possible function being used as a check constraint: a function that makes sure there is one row, and only one row in a table. At table creation, and the creation of the constraint, there are no rows in the table. So, even if the constraint is a valid one to have, it will fail. We use check constraint functions in a few places, and they work just fine for us (minus the case of trying to restore the database from a pg_dump archive). Is it impossible to treat check constraints similar to other constraints and make them deferrable, specifically in the restoration from a pg_dump archive? Is there a specific reason check constraints are handled differently from other constraints? Jonathan Scott On Thu, 26 Feb 2004 08:54:59 -0500 Tom Lane [EMAIL PROTECTED] wrote: Jonathan Scott [EMAIL PROTECTED] writes: The functions and tables create just fine, but when it gets to the COPY part of the sql script, it tries to load tables in what really is the wrong order. The check constraint is making sure there is a plan before there is a contract, yet pg_dump is trying to load the contract table before there is anything in the plan table. Shouldn't you be using a foreign key for that? I don't see any reasonable way that pg_dump can be expected to understand that a check constraint expresses a relationship between two tables. The semantics of check constraints aren't right for it anyway. All else being equal, I think the tables will be loaded in OID order, so a possible workaround is to create the plan table first. But an FK seems like a better answer. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] simple make check failures
Thanks for writing, Tom. I checked my system, and bison is reporting itself as 1.875. I am using SuSE 9.0, and I did have to upgrade to get that configuration message to go away, AND to make it compile. Could you recommend a way I could get bison to reproduce that message? Perhaps I should recompile bison from source, so it does not do any kind of backward compatibility stuff? (if you are in fact suggesting that is what the old bison reported) Jonathan Scott On Thu, 26 Feb 2004 00:17:14 -0500 Tom Lane [EMAIL PROTECTED] wrote: Jonathan Scott [EMAIL PROTECTED] writes: When using the current head of pgsql, I find four errors when running make check. They are all of the same nature, basically that the expected.out file has syntax error, where as psql is saying parse error. What bison version are you using? That was a bison change a few revs back. I'm actually surprised that CVS tip builds at all with a bison that predates that change. We are stressing bison because the grammar is so large. You really need bison 1.875 or later. (I think configure will warn about it if your bison is older, but the warning is easy to miss in the voluminous configure chatter :-() regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Check Constraints and pg_dump
Hello again, A project I am working on has been having problems with pg_dump's output, using 7.3. Our project's database includes functions that do constraint checking for us, as well as circular dependencies. We heard about the changes on the pgsql HEAD/7.5, and have given it a try. It fixed nearly all our problems; however, there is one that is cropping up that we feel should be reviewed: check constraints do not get deferred when loading the data back in to the database using pg_dump's default script. I have written a script which should be able to reproduce the problem we are encountering. We do not have binary data, so we just use the regular SQL output of pg_dump. The functions and tables create just fine, but when it gets to the COPY part of the sql script, it tries to load tables in what really is the wrong order. The check constraint is making sure there is a plan before there is a contract, yet pg_dump is trying to load the contract table before there is anything in the plan table. This may seem weird at first, as the plan table is referencing the contract table's PK. Our intention is to make sure that EVERY contract has at least one plan. Please feel free to ask me about this script and associated files. You will most likely want to edit the Test file, and make it point to the correct HEAD/7.5 run environment. I do not specify PGPORT/PGHOST etc in the file, so you will need to make sure you have those set in your environment. Jonathan Scott -- Jonathan Scott, Programmer, Vanten K.K. [EMAIL PROTECTED] Tel: 03-5919-0266 http://www.vanten.com Fax: 03-5919-0267 constraint_check_test.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
