[BUGS] BUG #5734: autovacuum_enabled input should be validated, standardized.
The following bug has been logged online: Bug reference: 5734 Logged by: Mark Stosberg Email address: m...@summersault.com PostgreSQL version: 9.0.1 Operating system: FreeBSD Description:autovacuum_enabled input should be validated, standardized. Details: The "autovacuum_enabled" storage parameter claims to be a boolean type: http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETAB LE-STORAGE-PARAMETERS ... but it fails to behave a normal boolean. Normally, you could set a boolean with a value of false, 'off' or 'f', but you would always get back a value of 'f' With this value, there is no translation. I think this kind of boolean should be handled like a standard PostgreSQL boolean. I noticed because the Slony code base has a hardcoded check for "autovacuum_enabled=off", when a false value could also be stored as autovacuum_enabled=f We should be able to rely on this value being always returned as "autovacuum_enabled='f'" just a normal boolean would. Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5734: autovacuum_enabled input should be validated, standardized.
"Mark Stosberg" writes: > The "autovacuum_enabled" storage parameter claims to be a boolean type: > http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETAB > LE-STORAGE-PARAMETERS > ... but it fails to behave a normal boolean. The parsing is the same as every other boolean parameter. What I think you are complaining about is not that, but that we don't rewrite the source string into some standard format. That seems rather impractical though. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5733: Strange planer behaviour with inherited tables
The following bug has been logged online: Bug reference: 5733 Logged by: Marcus Wirsing Email address: m...@hesotech.de PostgreSQL version: 9.0.1 Operating system: Windows XP 32 Description:Strange planer behaviour with inherited tables Details: when I execute the following script, the planer always makes a seq. scan over all child tables. drop schema if exists schema_0 cascade; drop schema if exists schema_1 cascade; drop schema if exists schema_2 cascade; create schema schema_0; create schema schema_1; create schema schema_2; CREATE TABLE schema_0.testtab ( entry bigserial NOT NULL, status integer, chnsetid integer, dt_package timestamp with time zone, dwell interval, cnt_mv integer, min_mv real[], PRIMARY KEY (entry) ); create table schema_1.testtab (primary key(entry)) inherits (schema_0.testtab); create table schema_2.testtab (primary key(entry)) inherits (schema_0.testtab); EXPLAIN SELECT entry FROM schema_0.testtab where entry > 1000 order by entry limit 1; result: "Limit (cost=62.73..62.73 rows=1 width=8)" " -> Sort (cost=62.73..64.66 rows=771 width=8)" "Sort Key: schema_0.testtab.entry" "-> Result (cost=0.00..58.88 rows=771 width=8)" " -> Append (cost=0.00..58.88 rows=771 width=8)" "-> Seq Scan on testtab (cost=0.00..19.63 rows=257 width=8)" " Filter: (entry > 1000)" "-> Seq Scan on testtab (cost=0.00..19.63 rows=257 width=8)" " Filter: (entry > 1000)" "-> Seq Scan on testtab (cost=0.00..19.63 rows=257 width=8)" " Filter: (entry > 1000)" when I remove the min_mv real[] the planer makes an index scan as expected. " -> Sort (cost=70.55..73.30 rows=1101 width=8)" "Sort Key: schema_0.testtab.entry" "-> Result (cost=7.09..65.05 rows=1101 width=8)" " -> Append (cost=7.09..65.05 rows=1101 width=8)" "-> Bitmap Heap Scan on testtab (cost=7.09..21.68 rows=367 width=8)" " Recheck Cond: (entry > 1000)" " -> Bitmap Index Scan on testtab_pkey (cost=0.00..7.00 rows=367 width=0)" "Index Cond: (entry > 1000)" "-> Bitmap Heap Scan on testtab (cost=7.09..21.68 rows=367 width=8)" " Recheck Cond: (entry > 1000)" " -> Bitmap Index Scan on testtab_pkey (cost=0.00..7.00 rows=367 width=0)" "Index Cond: (entry > 1000)" "-> Bitmap Heap Scan on testtab (cost=7.09..21.68 rows=367 width=8)" " Recheck Cond: (entry > 1000)" " -> Bitmap Index Scan on testtab_pkey (cost=0.00..7.00 rows=367 width=0)" "Index Cond: (entry > 1000)" -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
The following bug has been logged online: Bug reference: 5735 Logged by: Arturas Mazeika Email address: maze...@gmail.com PostgreSQL version: 9.0 Operating system: Windows Server 2003 Description:pg_upgrade thinks that it did not start the old server Details: 1. I am trying to migrate from Postgres 8.3 to 9.0. 2. I have installed both systems on Windows Server 2003. 3. I have stopped services of both 8.3 and 9.0. 4. I became postgres user with runas 5. I have started the pg_upgrade command and got an error: c:\windows\system32>pg_upgrade.exe --old-datadir "I:\PostgreSQL\8.3\data" --new- datadir "I:\PostgreSQL\9.0" --old-bindir "C:\Program Files (x86)\PostgreSQL\8.3\ bin" --new-bindir "C:\Program Files\PostgreSQL\9.0\bin" -l "c:\temp\log" Performing Consistency Checks - Checking old data directory (I:\PostgreSQL\8.3\data)ok Checking old bin directory (C:\Program Files (x86)\PostgreSQL\8.3\bin)ok Checking new data directory (I:\PostgreSQL\9.0) ok Checking new bin directory (C:\Program Files\PostgreSQL\9.0\bin)ok mapped win32 error code 2 to 2Trying to start old server .ok Unable to start old postmaster with the command: ""C:\Program Files (x86)\Postg reSQL\8.3\bin/pg_ctl" -l "nul" -D "I:\PostgreSQL\8.3\data" -o "-p 5432 -c autova cuum=off -c autovacuum_freeze_max_age=20" start >> "nul" 2>&1" Perhaps pg_hba.conf was not set to "trust". c:\windows\system32> The command starts the server (I can see that through process explorer, I can connect to the DB too after I get the error) 6. pg_hba.conf has a line: local all all trust This seems to be a bug, doesn't it? Did I misconfigure anything? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
On Sat, Oct 30, 2010 at 3:29 PM, Arturas Mazeika wrote: > > - > Checking old data directory (I:\PostgreSQL\8.3\data) ok > Checking old bin directory (C:\Program Files (x86)\PostgreSQL\8.3\bin)ok > Checking new data directory (I:\PostgreSQL\9.0) ok > Checking new bin directory (C:\Program Files\PostgreSQL\9.0\bin)ok I can't comment on the problem reported as I'm not that familiar with pg_upgrade, but from the paths above, it looks like you're trying to upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5732: parsing of: "WHERE mycol=123AND ..."
On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane wrote: > I experimented a bit with mysql's behavior, and it seems that (at least > in 5.1.51) what they do is treat "1and" or "2or" as if it were an > identifier. They're definitely not throwing an error, at least not on > I guess the eleant question is what the lexical elements section of the standard says about identifiers. It pretty clearly declares that they can't start with digits: ::= [ ... ] ::= | ::= !! See the Syntax Rules ::= !! See the Syntax Rules 1) An is any character in the Unicode General Category classes “Lu”, “Ll”, “Lt”, “Lm”, “Lo”, or “Nl”. NOTE 70 — The Unicode General Category classes “Lu”, “Ll”, “Lt”, “Lm”, “Lo”, and “Nl” are assigned to Unicode characters that are, respectively, upper-case letters, lower-case letters, title-case letters, modifier letters, other letters, and letter numbers. 2) An is U+00B7, “Middle Dot”, or any character in the Unicode General Category classes “Mn”, “Mc”, “Nd”, “Pc”, or “Cf”. NOTE 71 — The Unicode General Category classes “Mn”, “Mc”, “Nd”, “Pc”, and “Cf” are assigned to Unicode characters that are, respectively, nonspacing marks, spacing combining marks, decimal numbers, connector punctuations, and formatting codes. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5732: parsing of: "WHERE mycol=123AND ..."
Greg Stark writes: > On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane wrote: >> I experimented a bit with mysql's behavior, and it seems that (at least >> in 5.1.51) what they do is treat "1and" or "2or" as if it were an >> identifier. They're definitely not throwing an error, at least not on > I guess the eleant question is what the lexical elements section of > the standard says about identifiers. It pretty clearly declares that > they can't start with digits: Yeah. The key point IMO is that this *input* is not spec-compliant. So implementations can either throw an error, or define their own spec extension as to how to interpret it. I find mysql's behavior interesting mostly because it shows that throwing an error isn't necessarily common practice. Anybody want to try Oracle, DB2, etc? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5732: parsing of: "WHERE mycol=123AND ..."
MS SQL server 2008 has no problem with this: select * from client where CLIENT_ID = 12AND SNAME='Smith' Returns the expected row. PostgreSQL 9.0 has no problem with it either, again throwing no error and returning the expected result. Regards, Gary. On 30/10/2010 7:23 PM, Tom Lane wrote: Greg Stark writes: On Thu, Oct 28, 2010 at 5:20 PM, Tom Lane wrote: I experimented a bit with mysql's behavior, and it seems that (at least in 5.1.51) what they do is treat "1and" or "2or" as if it were an identifier. They're definitely not throwing an error, at least not on I guess the eleant question is what the lexical elements section of the standard says about identifiers. It pretty clearly declares that they can't start with digits: Yeah. The key point IMO is that this *input* is not spec-compliant. So implementations can either throw an error, or define their own spec extension as to how to interpret it. I find mysql's behavior interesting mostly because it shows that throwing an error isn't necessarily common practice. Anybody want to try Oracle, DB2, etc? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5736: 9.0.1 segmentation fault (sig11) during long-lived update
The following bug has been logged online: Bug reference: 5736 Logged by: Henry Email address: he...@cityweb.co.za PostgreSQL version: 9.0.1 Operating system: Linux Description:9.0.1 segmentation fault (sig11) during long-lived update Details: Hi, I'm using PostgreSQL 9.0.1 on centos 5.4. I'm running the following update (which takes a few days to run before I get the error - twice now): update tab1 set col1 = hashtext(col0) where col1 isnull; I'm running this update as part of an exercise to purge duplicates, before creating a UNIQUE index. Error from PG: server process (PID 19328) was terminated by signal 11: Segmentation fault terminating any other active server processes ... Before I run this again, what's the best way to proceed to get a core dump so I can run a gdb backtrace to provide more info? Simply 'ulimit -c 5242880' as user postgres and restart PG? Thanks -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5732: parsing of: "WHERE mycol=123AND ..."
On Sat, Oct 30, 2010 at 11:23 AM, Tom Lane wrote: > > Anybody want to try Oracle, DB2, etc? Oracle seems to behave like us: SQL> select * from (select 1 as x from dual) where 1=1and x=1; X -- 1 -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5736: 9.0.1 segmentation fault (sig11) during long-lived update
"Henry" writes: > Before I run this again, what's the best way to proceed to get a core dump > so I can run a gdb backtrace to provide more info? Simply 'ulimit -c > 5242880' as user postgres and restart PG? I'd use "ulimit -c unlimited", myself, rather than making arbitrary assumptions about how big the corefile might be. Also, make sure the ulimit command is effective in the shell that will actually launch the postmaster. This can be tricky if your PG launch script uses "su". If you're using the RH or PGDG RPMs' initscript, I'd suggest putting the ulimit command in ~postgres/.bash_profile. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
Hi Dave, Thanks for the info, this explains a lot. Yes, I am upgrading from the 32bit version to the 64bit one. We have pretty large databases (some over 1 trillion of rows, and some containing large documents in blobs.) Giving a bit more memory than 4GB limit to Postgres was what we were long longing for. Postgres was able to handle large datasets (I suppose it uses something like long long (64bit) data type in C++) and I hoped naively that Postgres would be able to migrate from one version to the other without too much trouble. I tried to pg_dump one of the DBs with large documents. I failed with out of memory error. I suppose it is rather hard to migrate in my case :-( Any suggestions? Thanks, arturas On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
