Re: [HACKERS] Validating problem in the isn contrib module
In response to Andreas 'ads' Scherbaum : > > Hello all, > > > test=# select is_valid('978-3-937514-69-7'::isbn13); > is_valid > -- > t > (1 row) Nice advertisement for your book... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use array in a dynamic statement
I don't see how the problem can be solved by merging the function logic into the calling function. The int[][] array is supplied by user, and I still have the issue to pass it into a dynamic statement, no matter in which function, unless I serialize it to string and deserialize back to int[][]. - Original Message From: Robert Haas To: Sophie Yang Cc: Pavel Stehule ; pgsql-hackers@postgresql.org Sent: Thursday, March 5, 2009 6:51:48 PM Subject: Re: [HACKERS] Use array in a dynamic statement On Thu, Mar 5, 2009 at 8:32 PM, Sophie Yang wrote: > > I was wondering why USING clause is not supported in pl/pgsql dynamic > statement. Serialization is the option I tried to avoid, but it seems there > is no better approach available. > > Just to say a few more about the usage of my function. In dag_tree_1, (rid, > rtid) is the primary key, which identifies a node in a tree structure. The > idx field is a kind of dewy index. for example: > rid rtid idx > 1123 1 .0006.0033 > 3231 1 .0006 > 786 6 .0007.8853 > 80923 2 .0007.8853.2382 > > The function takes in a list of rid and rtid pair (nids), sort them by the > length of the dewy index, which is equivalent to sort the nodes by their tree > depth. That's what I try to achieve. Maybe someone has different idea to > implement the function? Well the function is pretty simple. Maybe you could merge the logic encapsulated by the function into the containing query? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Additional DTrace Probes
The attached patch contains the probes (originally came from Theo Schlossnagle) that were removed from the previous submitted patch. Zdenek had some concerns about the way the probes were implemented http://archives.postgresql.org/pgsql-hackers/2008-07/msg01168.php. If there are specific recommendations, I'd be more than happy to make the changes and get them resubmitted. -Robert Index: src/backend/access/transam/slru.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/slru.c,v retrieving revision 1.45 diff -u -3 -p -r1.45 slru.c --- src/backend/access/transam/slru.c 1 Jan 2009 17:23:36 - 1.45 +++ src/backend/access/transam/slru.c 6 Mar 2009 04:01:56 - @@ -57,6 +57,7 @@ #include "storage/fd.h" #include "storage/shmem.h" #include "miscadmin.h" +#include "pg_trace.h" /* @@ -372,6 +373,7 @@ SimpleLruReadPage(SlruCtl ctl, int pagen { SlruShared shared = ctl->shared; + TRACE_POSTGRESQL_SLRU_READPAGE_START((uintptr_t)ctl, pageno, write_ok, xid); /* Outer loop handles restart if we must wait for someone else's I/O */ for (;;) { @@ -399,6 +401,7 @@ SimpleLruReadPage(SlruCtl ctl, int pagen } /* Otherwise, it's ready to use */ SlruRecentlyUsed(shared, slotno); + TRACE_POSTGRESQL_SLRU_READPAGE_DONE(slotno); return slotno; } @@ -446,6 +449,7 @@ SimpleLruReadPage(SlruCtl ctl, int pagen SlruReportIOError(ctl, pageno, xid); SlruRecentlyUsed(shared, slotno); + TRACE_POSTGRESQL_SLRU_READPAGE_DONE(slotno); return slotno; } } @@ -470,6 +474,8 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, SlruShared shared = ctl->shared; int slotno; + TRACE_POSTGRESQL_SLRU_READPAGE_READONLY((uintptr_t)ctl, pageno, xid); + /* Try to find the page while holding only shared lock */ LWLockAcquire(shared->ControlLock, LW_SHARED); @@ -511,6 +517,8 @@ SimpleLruWritePage(SlruCtl ctl, int slot int pageno = shared->page_number[slotno]; boolok; + TRACE_POSTGRESQL_SLRU_WRITEPAGE_START((uintptr_t)ctl, pageno, slotno); + /* If a write is in progress, wait for it to finish */ while (shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS && shared->page_number[slotno] == pageno) @@ -525,7 +533,10 @@ SimpleLruWritePage(SlruCtl ctl, int slot if (!shared->page_dirty[slotno] || shared->page_status[slotno] != SLRU_PAGE_VALID || shared->page_number[slotno] != pageno) + { + TRACE_POSTGRESQL_SLRU_WRITEPAGE_DONE(); return; + } /* * Mark the slot write-busy, and clear the dirtybit. After this point, a @@ -569,6 +580,8 @@ SimpleLruWritePage(SlruCtl ctl, int slot /* Now it's okay to ereport if we failed */ if (!ok) SlruReportIOError(ctl, pageno, InvalidTransactionId); + + TRACE_POSTGRESQL_SLRU_WRITEPAGE_DONE(); } /* @@ -593,6 +606,8 @@ SlruPhysicalReadPage(SlruCtl ctl, int pa SlruFileName(ctl, path, segno); + TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_START((uintptr_t)ctl, path, pageno, slotno); + /* * In a crash-and-restart situation, it's possible for us to receive * commands to set the commit status of transactions whose bits are in @@ -607,6 +622,7 @@ SlruPhysicalReadPage(SlruCtl ctl, int pa { slru_errcause = SLRU_OPEN_FAILED; slru_errno = errno; + TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno); return false; } @@ -614,6 +630,7 @@ SlruPhysicalReadPage(SlruCtl ctl, int pa (errmsg("file \"%s\" doesn't exist, reading as zeroes", path))); MemSet(shared->page_buffer[slotno], 0, BLCKSZ); + TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(true, slru_errcause, slru_errno); return true; } @@ -622,6 +639,7 @@ SlruPhysicalReadPage(SlruCtl ctl, int pa slru_errcause = SLRU_SEEK_FAILED; slru_errno = errno; close(fd); + TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno); return false; } @@ -631,6 +649,7 @@ SlruPhysicalReadPage(SlruCtl ctl, int pa slru_errcause = SLRU_READ_FAILED; slru_errno = errno; close(fd); + TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno);
Re: [HACKERS] Use array in a dynamic statement
On Thu, Mar 5, 2009 at 8:32 PM, Sophie Yang wrote: > > I was wondering why USING clause is not supported in pl/pgsql dynamic > statement. Serialization is the option I tried to avoid, but it seems there > is no better approach available. > > Just to say a few more about the usage of my function. In dag_tree_1, (rid, > rtid) is the primary key, which identifies a node in a tree structure. The > idx field is a kind of dewy index. for example: > rid rtid idx > 1123 1 .0006.0033 > 3231 1 .0006 > 786 6 .0007.8853 > 80923 2 .0007.8853.2382 > > The function takes in a list of rid and rtid pair (nids), sort them by the > length of the dewy index, which is equivalent to sort the nodes by their tree > depth. That's what I try to achieve. Maybe someone has different idea to > implement the function? Well the function is pretty simple. Maybe you could merge the logic encapsulated by the function into the containing query? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN, partial matches, lossy bitmaps
On Thu, Mar 5, 2009 at 6:35 PM, Tom Lane wrote: > Attached is the remainder of the patch with relatively minor fixes. > The main change I made is to get rid of the changes in gincostestimate; > I agree with Robert that it's probably inappropriate to consider the > current pending-list size during planning. I haven't really reviewed > any of the rest of it; this is just to have a clean patch against HEAD. The changes to config.sgml are not good English and contain typographical errors. It could also be a bit more informatiave, maybe something like: This parameter also specifies the number of insert or updated tuples needed to trigger VACUUM on a GIN index. GIN indexes require VACUUM after insert or update operations because newly inserted tuples are initially stored in an unsorted pending list. I still think removing index scans entirely is short-sighted - but I may be outvoted (then again, no one other than Tom has really expressed an opinion one way or the other, and I initially agreed with him until I thought about the performance aspects some more). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use array in a dynamic statement
I was wondering why USING clause is not supported in pl/pgsql dynamic statement. Serialization is the option I tried to avoid, but it seems there is no better approach available. Just to say a few more about the usage of my function. In dag_tree_1, (rid, rtid) is the primary key, which identifies a node in a tree structure. The idx field is a kind of dewy index. for example: rid rtid idx 1123 1 .0006.0033 3231 1 .0006 786 6 .0007.8853 80923 2 .0007.8853.2382 The function takes in a list of rid and rtid pair (nids), sort them by the length of the dewy index, which is equivalent to sort the nodes by their tree depth. That's what I try to achieve. Maybe someone has different idea to implement the function? Thanks, Sophie - Original Message From: Pavel Stehule To: Sophie Yang Cc: pgsql-hackers@postgresql.org Sent: Thursday, March 5, 2009 12:06:24 AM Subject: Re: [HACKERS] Use array in a dynamic statement Hello you can't to use parameters inside literal. There hasn't any sense (to 8.3, 8.4 will support USING). you have to use serialisation to string and quoting. some like CREATEOR REPLACE FUNCTION foo(int[]) RETURNSSETOF int AS $$ DECLAREr record; BEGIN FOR r IN EXECUTE 'SELECT (' || quote_literal($1::text) || '::int[])[i] AS x FROM generate_series(1, array_upper(' || quote_literal($1::text) || '::int[],1)) g(i)' LOOP RETURN NEXT r.x; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(ARRAY[1,2,3]); CREATE OR REPLACE FUNCTION foo84(int[]) RETURNS SETOF int AS $$ DECLARE r record; BEGIN FOR r IN EXECUTE 'SELECT $1[i] AS x FROM generate_series(1, array_upper($1,1)) g(i)' USING $1 LOOP RETURN NEXT r.x; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo84(ARRAY[1,2,3]); regards Pavel Stehule 2009/3/5 Sophie Yang : > > Hi, > > I am trying to implement a PL/PgSQL function as following: > CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR) > RETURNS varchar[] > AS $$ > DECLARE > result varchar[]; > BEGIN > > EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1), > array_upper($1,1)) AS s(i), ' >||tbl_name||' t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER > BY length(t.idx))' > INTO result; > > RETURN result; > END; > $$ LANGUAGE plpgsql; > > I got an error "ERROR: there is no parameter $1" when I test the function > with: > select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170, > 1}}'::int[][], 'd_tree_1'); > > The error is understandable, but my question is how to supply the int[][] > array into the dynamic SQL? > > To help understand the dynamic statement, the structure of d_tree_1 is (rid, > rtid, idx). The PK is (rid, rtid) pair. > > If the tbl_name is fixed, the following function works well: > CREATE OR REPLACE FUNCTION sort_by_d_idx2 (nids INT[][]) > RETURNS varchar[] > LANGUAGE SQL > AS $$ > SELECT ARRAY( >SELECT t.idx >FROM >generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), > d_tree_1 t >WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid >ORDER BY length(t.idx) > ); > $$; > > Unfortunately, the tbl_name is determined at query time. > > Please help. > > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Validating problem in the isn contrib module
Hello all, i'm playing around with the isn contrib module and ran into an annoying problem. The module defines an is_valid() function which obviously is intended to check the validity of an ISBN number. Makes sense to have such a function because if the user mistyped the number the application can raise an error. Now it seems that instead of the application PostgreSQL is raising the error: test=# select is_valid('978-3-937514-69-7'::isbn13); is_valid -- t (1 row) test=# select is_valid('978-3-937514-69-6'::isbn13); ERROR: invalid check digit for ISBN number: "978-3-937514-69-6", should be 7 ROW 1: select is_valid('978-3-937514-69-6'::isbn13); ^ The first ISBN is valid and the validator function returns 't', that's fine. The second ISBN is invalid, i mistyped one number. The expected output is 'f', PG is not supposed to raise an error and break my entire transaction. Is this just a bug or is this intended behaviour. And if it's not a bug, how can i validate an ISBN number in my application - without raising an error? Thank you & kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Can we drop ABSTIME?
All, We've been carrying this warning in our docs since 7.4: "The key word ABSTIME is ignored for historical reasons: In very old releases of PostgreSQL, invalid values of type abstime were emitted as Invalid Abstime. This is no longer the case however and this key word will likely be dropped in a future release." So given that it's been depreciated for 5 versions, maybe it's time to rip it out? --Josh (thanks to rmxz for finding this) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN, partial matches, lossy bitmaps
Teodor Sigaev writes: > Changes since 28.2 > (http://archives.postgresql.org/message-id/499b0ffa.8040...@sigaev.ru) > - fixes/changes pointed by Robert > (http://archives.postgresql.org/pgsql-hackers/2009-02/msg00987.php) > - gingetbitmap will never throw error about lossiness of bitmap, it will > return > lossy bitmap even it was a prefix search. > - remove tbm_check_tuple/tbm_has_lossy/tbm_max_non_lossy methods because they > become unused > - add new method tbm_add_page(TIDBitmap*, BlockNumber) to add the whole page > to > the TIDBitmap. I cleaned up and applied the planner part of this, since that seems reasonably useful in its own right for experimental index AMs, regardless of where we settle out for GIN. (The "cleanup" mostly consisted of fixing it to not make extra calls to find_usable_indexes --- that's an expensive function, and there's no very good reason to run it another time rather than separating out the indexes afterwards.) Attached is the remainder of the patch with relatively minor fixes. The main change I made is to get rid of the changes in gincostestimate; I agree with Robert that it's probably inappropriate to consider the current pending-list size during planning. I haven't really reviewed any of the rest of it; this is just to have a clean patch against HEAD. regards, tom lane bintz5v7oad5A.bin Description: fast_insert_gin-0.30.gz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
On Thu, Mar 05, 2009 at 08:19:05PM +0100, Magnus Hagander wrote: > Peter Eisentraut wrote: > > On Thursday 05 March 2009 18:04:42 Joshua Tolley wrote: > >> As an aside, is access() adequately portable, ok to use within the > >> backend, etc.? I just sort of took a shot in the dark. > > > > Using access() is usually not a good idea. In this case it would be better > > to > > check the return of the actual open() call for EPERM (or the equivalent for > > fopen(), whatever is used). > > That's what we do in the proper fix in HEAD. It requires an API change > to backport it... > > Given that I think this is the first time we've heard of this issue, I'm > thinking we should probably just not bother to backpatch it. I'm inclined to agree, FWIW. - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Jaime Casanova wrote: > On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati wrote: > >> Guillaume Smet ha scritto: >> >>> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut wrote: >>> The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. >>> Sure. See my previous answer on -hackers (I don't think this >>> discussion belong to -bugs) and especially the discussion in the >>> archives about Jonas' patch. >>> >> I recently had a similar problem when I added some domains to the >> application. ALTER TABLE ... TYPE varchar_dom was leading to a full >> table rewrite even though the underlying type definition were exactly >> the same (i.e. varchar(64)). I can live with it, but I suppose this fix >> might be related to the varlen one. >> >> > > ALTER TABLE ... TYPE does cause a table rewrite even if new_type = > old_type, and that is actually useful... > for example when you add a fillfactor to an existing table that > fillfactor will not affect the existing data until you rewrite the > table and a convenient way is exactly using ALTER TABLE ... TYPE. > Well, while this behaviour is well-known for PostgreSQL, this is actually an abuse of syntax. If there are legitimate requirements for rewriting a table, then there should be explicit syntax for such a feature, like "ALTER TABLE ... REWRITE". Rewriting a table in case of "ALTER TABLE ... TYPE" is, by the semantics of that statement, just a side-effect, which may or may not happen, depending on how optimized the DBMS is. It is bad design to avoid optimization just because an unnecessary side-effect would be optimized away. > now, back to the problem... is not easier to define a column as TEXT > and to put a check to constraint the length? if you wanna change the > constraint that will be almost free No. Is it possible to change the column type from VARCHAR(5) to TEXT without a table-rewrite penalty? ciao, Xuân.
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 3:27 PM, Xuân Baldauf wrote: > > > Well, while this behaviour is well-known for PostgreSQL, this is actually an > abuse of syntax. If there are legitimate requirements for rewriting a table, > then there should be explicit syntax for such a feature, like "ALTER TABLE > ... REWRITE". Rewriting a table in case of "ALTER TABLE ... TYPE" is, by the > semantics of that statement, just a side-effect, which may or may not > happen, depending on how optimized the DBMS is. It is bad design to avoid > optimization just because an unnecessary side-effect would be optimized > away. > note that this is my opinion and not represent the PGDG (Postgresql Global Development Group) opinion > now, back to the problem... is not easier to define a column as TEXT > and to put a check to constraint the length? if you wanna change the > constraint that will be almost free > > No. Is it possible to change the column type from VARCHAR(5) to TEXT without > a table-rewrite penalty? > > the idea is to make that change once (and to create new tables just with TEXT) and then you can make ALTER TABLE ... ADD CHECK (length(column) = a_value) as many times as you want without the need for a table rewrite -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 2:46 PM, Kevin Grittner wrote: > >> now, back to the problem... is not easier to define a column as TEXT >> and to put a check to constraint the length? if you wanna change the >> constraint that will be almost free > > Thanks for the interesting suggestion. I'm not sure I'd want to go > there for various reasons; but even if I wanted to go that route, how > would I modify that constraint without causing the whole table to be > scanned for compliance? > the table will be scanned but not rewritten -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
2009/3/5 André Volpato : > Guedes my friend, that doesnt work either. ":( > First, I put back only the "define my_cbrt" line in float.c, and then tryed > to configure with all the flags above. > > The "Undefined symbols" errors keep coming. > > As Thomas pointed out, I think my AIX is missing libm. > > I will try to install it and post again when I have some news. Please, install the linuxtoolbox for AIX, try compile again and tell us. It means that somethings is missing in your box. []s Dickson S. Guedes - mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
>>> Jaime Casanova wrote: > the table will be scanned but not rewritten That can still be a very long time on some tables. And there would still be the issue of dodging all the brickbats thrown at me by developers whose tools use the system tables to limit the number of characters a user is allowed to type into an application. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
Fujii Masao writes: > BTW, I found that backup.sgml still had the description of log_restartpoints. > Here is the patch to remove it. Applied, thanks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
Dickson S. Guedes escreveu: 2009/3/4 André Volpato : I´m trying to install postgres 8.3.6 at AIX 5.3, and I got a declaration error that I presume its related to this [1] thread. Here´s some info: - Bladecenter IBM JS22 - PowerPC_POWER6 quad 4GHZ 64 bits - AIX 5.3.0 - Postgresql 8.3.6 - gcc version 4.2.0 - GNU Make 3.80 (...) Here: postg...@db01 $ id uid=204(postgres) gid=1(staff) postg...@db01 $ export CC="gcc" postg...@db01 $ export CFLAGS="-maix64" postg...@db01 $ export LDFLAGS="-Wl,-bbigtoc" postg...@db01 $ export OBJECT_MODE=64 postg...@db01 $ export AR="ar -X64" postg...@db01 $ ./configure --enable-integer-datetimes --prefix=/srv/postgresql/8.3.6 postg...@db01 $ make ... ... And all is done fine. Course, all above is for 64 bits compilling. How you are trying that? Guedes my friend, that doesnt work either. First, I put back only the "define my_cbrt" line in float.c, and then tryed to configure with all the flags above. The "Undefined symbols" errors keep coming. As Thomas pointed out, I think my AIX is missing libm. I will try to install it and post again when I have some news. -- []´s, ACV
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
>>> Jaime Casanova wrote: > ALTER TABLE ... TYPE does cause a table rewrite even if new_type = > old_type, and that is actually useful... > for example when you add a fillfactor to an existing table that > fillfactor will not affect the existing data until you rewrite the > table and a convenient way is exactly using ALTER TABLE ... TYPE. I find that to be exactly as useful as it would be to have a table rewrite if I added a new null-capable column, and somewhat less useful than it would be have a table rewrite on dropping a column. Maintaining the function of this clever trick should not be the basis of imposing a burden on relatively common maintenance operations. > now, back to the problem... is not easier to define a column as TEXT > and to put a check to constraint the length? if you wanna change the > constraint that will be almost free Thanks for the interesting suggestion. I'm not sure I'd want to go there for various reasons; but even if I wanted to go that route, how would I modify that constraint without causing the whole table to be scanned for compliance? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
Thomas Pundt escreveu: Hi, André Volpato schrieb: [...] gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline [...] utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a -Wl,-bE:../../src/backend/postgres.imp -lld -o postgres ld: 0711-317 ERROR: Undefined symbol: .lrint ld: 0711-317 ERROR: Undefined symbol: ._isnan ld: 0711-317 ERROR: Undefined symbol: ._isinff ld: 0711-317 ERROR: Undefined symbol: .sqrt ld: 0711-317 ERROR: Undefined symbol: .tan ld: 0711-317 ERROR: Undefined symbol: .sin ld: 0711-317 ERROR: Undefined symbol: .cos [...] What can I do next ? I don't have a clue about AIX, but that certainly looks like it's missing a -lm now. I cant put -lm in LDFLAGS, the system cant find libm. Well, I´m gonna install libm from the CD and try again. Thanks. -- []´s, ACV -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera writes: > Hopefully this is the last version of this patch. A few more comments would help --- in particular the header comment for kwlist.h should explain that the calling file is supposed to define PG_KEYWORD appropriately for its needs. I also wonder whether Greg isn't right that it would be better if the header contained *only* the PG_KEYWORD macros, rather than presupposing that the caller wants to build a constant table named ScanKeywords with them. In general though it's certainly cleaner than the old way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati wrote: > Guillaume Smet ha scritto: >> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut wrote: >>> The question is how you want to implement this in a data type independent >>> fashion. You can't assume that increasing the typmod is a noop for all data >>> types. >> >> Sure. See my previous answer on -hackers (I don't think this >> discussion belong to -bugs) and especially the discussion in the >> archives about Jonas' patch. > > I recently had a similar problem when I added some domains to the > application. ALTER TABLE ... TYPE varchar_dom was leading to a full > table rewrite even though the underlying type definition were exactly > the same (i.e. varchar(64)). I can live with it, but I suppose this fix > might be related to the varlen one. > ALTER TABLE ... TYPE does cause a table rewrite even if new_type = old_type, and that is actually useful... for example when you add a fillfactor to an existing table that fillfactor will not affect the existing data until you rewrite the table and a convenient way is exactly using ALTER TABLE ... TYPE. now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
Peter Eisentraut wrote: > On Thursday 05 March 2009 18:04:42 Joshua Tolley wrote: >> As an aside, is access() adequately portable, ok to use within the >> backend, etc.? I just sort of took a shot in the dark. > > Using access() is usually not a good idea. In this case it would be better > to > check the return of the actual open() call for EPERM (or the equivalent for > fopen(), whatever is used). That's what we do in the proper fix in HEAD. It requires an API change to backport it... Given that I think this is the first time we've heard of this issue, I'm thinking we should probably just not bother to backpatch it. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
On Thursday 05 March 2009 18:04:42 Joshua Tolley wrote: > As an aside, is access() adequately portable, ok to use within the > backend, etc.? I just sort of took a shot in the dark. Using access() is usually not a good idea. In this case it would be better to check the return of the actual open() call for EPERM (or the equivalent for fopen(), whatever is used). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Expanding the length of a VARCHAR column should not induce a table rewrite
Took bugs list off, as this is clearly not a bug. >>> Matteo Beccati wrote: > I recently had a similar problem when I added some domains to the > application. ALTER TABLE ... TYPE varchar_dom was leading to a full > table rewrite even though the underlying type definition were exactly > the same (i.e. varchar(64)). I can live with it, but I suppose this fix > might be related to the varlen one. Been there. We also occasionally increase the length of a verchar-based domain. The process could be made faster and more convenient by avoiding rewrites when possible. On particularly large tables I've sometimes ventured into direct updates to the system tables for these. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Operators based on non-IMMUTABLE functions
Joshua Tolley writes: > I've recently run into a problem with a datatype whose operators are > based on functions not marked IMMUTABLE. Although there might be good > reasons to have such a thing, it seems like it might be a valuable > warning message if you create an operator based on an non-IMMUTABLE > function. Comments? No, it wouldn't be a good idea. There are plenty of such operators. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Operators based on non-IMMUTABLE functions
On Thu, 2009-03-05 at 11:27 -0700, Joshua Tolley wrote: > I've recently run into a problem with a datatype whose operators are > based on functions not marked IMMUTABLE. Although there might be good > reasons to have such a thing, it seems like it might be a valuable > warning message if you create an operator based on an non-IMMUTABLE > function. Comments? > When I do: select oprname, oprcode, provolatile from pg_operator , pg_proc where pg_proc.oid::regclass = oprcode and provolatile <> 'i'; There are a bunch of operators related to TIMESTAMPTZ and full text search that are marked as STABLE. I don't know what the guidelines are for using a WARNING, but the examples that come to mind are generally things that can be fixed. For instance, if you get a WARNING for using non-standard backslash escapes, you can fix it by using E''. However, I agree that forgetting to mark functions correctly is a pretty significant problem. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Operators based on non-IMMUTABLE functions
I've recently run into a problem with a datatype whose operators are based on functions not marked IMMUTABLE. Although there might be good reasons to have such a thing, it seems like it might be a valuable warning message if you create an operator based on an non-IMMUTABLE function. Comments? - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] pg_restore -m failing
On Tue, 3 Mar 2009, Andrew Dunstan wrote: Date: Tue, 03 Mar 2009 11:02:35 -0500 From: Andrew Dunstan To: o...@pyrenet.fr Cc: pgsql-hackers list Subject: Re: [HACKERS] pg_restore -m failing o...@pyrenet.fr wrote: hi all, After Andrew corrected the bug I found on pg_restore (-C -m misbehaving one with the other), I played again yesterday. before the end of restore, I get this : pg_restore: launching item 4202 INDEX idx_typeper_actif pg_restore: [custom archiver] could not close archive file: Argument incorrect pg_restore: *** aborted because of error pg_restore: finished item 4202 INDEX idx_typeper_actif pg_restore: [archiver] worker process failed: exit code 1 pg_restore: *** aborted because of error Any idea? this is on unixware That seems very strange. Is it repeatable. it is on unixware 100% of the time, not on linux. I wonder what theincorrect argument was? my guess would be the file desc. Maybe the memory got clobbered somewhere. probably, but I could'nt proove it. Actually, while thinking about this it occurred to me that we are probably wasting a few cycles reopening the archive where we don't need to - it should only be necessary where we are restoring a data member, ISTM. I'll look at that further. take your time! cheers andrew regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: o...@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Guillaume Smet ha scritto: > On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut wrote: >> The question is how you want to implement this in a data type independent >> fashion. You can't assume that increasing the typmod is a noop for all data >> types. > > Sure. See my previous answer on -hackers (I don't think this > discussion belong to -bugs) and especially the discussion in the > archives about Jonas' patch. I recently had a similar problem when I added some domains to the application. ALTER TABLE ... TYPE varchar_dom was leading to a full table rewrite even though the underlying type definition were exactly the same (i.e. varchar(64)). I can live with it, but I suppose this fix might be related to the varlen one. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 04, 2009 at 02:35:52PM -0800, Josh Berkus wrote: > Tom Lane wrote: >> Magnus Hagander writes: >>> I think this sounds a lot like another request for a set of system views >>> with nicer names. >> >> What's the state of the newsysviews project, anyway? I don't recall >> hearing much about it lately. > > Final verdict was that we need to make it integrate better with > information_schema. At that point, our crew kinda ran out of energy and > it's been on hold ever since. But we've been talking about reviving it > again. The information schema has things mandated by the SQL standard, and so we really need to stay inside the lines with it. By its nature, it must hide "implementation details" which newsysviews can expose, so now that there's a track record of 5 versions of compatible newsysviews, I think we should make it a schema that ships with every database, starting with 8.5. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: B-Tree emulation for GIN
The GIN_EXTRACT_VALUE macro returns a pointer to a static 'entries' variable. That doesn't seem safe. Is it really never possible to have to two GIN searches in a plan, both calling and using the value returned by extractValue simultaneously? In any case that seems like a pretty weak assumption. Fixed. You might want to declare extra_data as just "void *", instead of an array of pointers. The data type implementation might want to store something there that's not per-key, but applies to the whole query. I see that you're passing it to comparePartial, but that seems to be just future-proofing. What kind of a data type are you envisioning that would wildspeed module (http://www.sigaev.ru/cvsweb/cvsweb.cgi/wildspeed/) - for each key from it's needed to store some info. Right now it's coded directly in Datum, but it looks ugly (at least for me). It's possible to clarify interface by introducing new type: typedef void* OpaquePtr; Then, prototypes will be: extractQuery(..., OpaquePtr* extra_data[]) consistent(, OpaquePtr extra_data[]) comparePartial(..., OpaquePtr extra_data) Or another option: partial match feature is new for 8.4, so we could change interface: typedef struct KeyData { bool pmatch, void *extra_data; } KeyData; Datum *extractQuery(Datum query, int32 *nkeys, StrategyNumber n, KeyData* data[]) bool consistent(bool check[], StrategyNumber n, Datum query, bool *recheck, KeyData data[]) comparePartial(Datum partial_key, Datum key, KeyData *data); make use of it? It seems that you could pass the same information in the partial key Datum itself that extractQuery returns. You're currently using it as a way to avoid some palloc's in gin_tsquery_consistent(). That seems like a pretty dirty hack. I doubt there's any meaningful performance advantage from that, but if there is, I think you could use a statically allocated array instead. It's easy to un-dirty that hack, but before I'd like to see your comments about thoughts above. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ btree_gin-0.11.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
On Thu, Mar 05, 2009 at 09:47:55AM -0500, Tom Lane wrote: > Magnus Hagander writes: > > Yeah, the big question is if we want to backport something like this at > > all... Thoughts? > > The issue never even came up before, so I'd vote to not take any risks > for it. How often do people mess up the protections on pg_hba.conf? Apparently I do :) Whether I'm the only one or not, I can't say. I realize this wouldn't protect anyone from, say, syntax errors, which certainly are more common. As an aside, is access() adequately portable, ok to use within the backend, etc.? I just sort of took a shot in the dark. - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] [BUG] Column-level privileges on inherited tables
Stephen Frost writes: > KaiGai, > * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: >> The attached patch fixes the matter. >> It fixes up attribute number of child relation when it is extracted. > Thanks! It looks good to me, but we'll need Tom or some other > committer to review it and commit it, of course. It's duplicating (rather badly) the work done by make_inh_translation_list. I'll see about refactoring it to make use of that translation data. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
Magnus Hagander writes: > Yeah, the big question is if we want to backport something like this at > all... Thoughts? The issue never even came up before, so I'd vote to not take any risks for it. How often do people mess up the protections on pg_hba.conf? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for the MUST time zone (Mauritius Summer Time)
Xavier Bugaud wrote: On Thursday 05 March 2009 12:31:17 Heikki Linnakangas wrote: Xavier Bugaud wrote: This trivial patch allows PostgreSQL to understand the MUST (Mauritius Summer Time) time zone that is in used since 2008-11. ... *** src/timezone/tznames/Default1 May 2008 20:05:08 - 1.6 --- src/timezone/tznames/Default5 Mar 2009 07:03:19 - *** *** 640,645 --- 640,647 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUT 18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) Shouldn't that new line be "MUST" then? My mistake, sorry... Please find bellow the new patches. Thanks! Committed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN, partial matches, lossy bitmaps
If we remove the support for regular, non-bitmap, index scans with GIN, that could be cleaned up as well. Even if we don't do that, gingetbitmap should not error when the bitmap becomes lossy, but just return the lossy bitmap. Changes since 28.2 (http://archives.postgresql.org/message-id/499b0ffa.8040...@sigaev.ru) - fixes/changes pointed by Robert (http://archives.postgresql.org/pgsql-hackers/2009-02/msg00987.php) - gingetbitmap will never throw error about lossiness of bitmap, it will return lossy bitmap even it was a prefix search. - remove tbm_check_tuple/tbm_has_lossy/tbm_max_non_lossy methods because they become unused - add new method tbm_add_page(TIDBitmap*, BlockNumber) to add the whole page to the TIDBitmap. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ fast_insert_gin-0.29.2.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)
KaiGai Kohei wrote: Heikki, Thanks for your comments. Heikki Linnakangas wrote: Ok, I've taken a quick look at this too. My first impression is that this is actually not a very big patch. Much much smaller than I was afraid of. It seems that dropping the row-level security and the other change you've already done have helped a great deal. My first question is, why does the patch need the walker implementation to gather all the accessed tables and columns? Can't you hook into the usual pg_xxx_aclcheck() functions? In fact, Peter asked that same question here: http://archives.postgresql.org/pgsql-hackers/2009-01/msg02295.php (among other things). Many things have changed since, but I don't think that question has been adequately answered. Different handling of permissions on views was mentioned, but I think that could be handled with just a few extra checks in the rewriter or executor. Yes, one major reason is to handle views. SE-PostgreSQL need to check permissions on after it is extracted. : I'll check some of corner cases, such as inherited tables, COPY statement, trigger invocations and others, to consider whether your suggestion is possible, or not. Please wait for a while to fix my attitude. Heikki, I now feel tempted by an idea to utilize the facilities of table/column-level privileges. One matter was "use" permission, but I can agree to integrate it into "select" permission as the original design did. The other is view. When we use a view in the query, it is extracted as a subquery and its query tree is fetched from pg_rewrite.ev_action which is already parsed. It means we need to ensure the parsed representation is not manipulated. The simplest solution is to prevent updating the pg_rewrite.ev_action by hand when SE-PostgreSQL is enabled. I think smaller hard-wired rules are better, but it is a very corner-case and its benefit cannot be ignorable. - It enables to reduce the "walker" code from sepgsql/checker.c. (I guess it makes reduce a few hundreds lines.) - It helps to maintain code to pick up what tables/columns are accessed. If nobody disagree it, I'll integrate "use" permission into "select" and remove the "walker" code from sepgsql/checker.c due to the next Monday. It affects on sepgsql/checker.c, but I expect little changes on others. I'm happy, if you don't stop reviewing patches except for checker.c. Thanks, -- KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Column-level privileges on inherited tables
KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: > The attached patch fixes the matter. > It fixes up attribute number of child relation when it is extracted. Thanks! It looks good to me, but we'll need Tom or some other committer to review it and commit it, of course. Thanks again, Stephen signature.asc Description: Digital signature
Re: [HACKERS] building pg_dump doesn't work
On Thu, Mar 5, 2009 at 1:12 PM, Alvaro Herrera wrote: > Tom Lane wrote: >> Alvaro Herrera writes: >> >> Seems doable. >> >> > Attached. >> >> The TWO_MEMBER_SCANKEYWORD business seems a bit dangerous --- if the >> header file is read without having #defined that correctly, bad things >> will happen. It might be better to leave that out, always define the >> struct the same, and just have pg_dump define PG_KEYWORD to fill the >> value field with zero. Given alignment considerations, you're not >> saving any space by omitting the field anyhow. > > Fixed. > > I also added #include type.h to the ecpg keywords.c file, which means we > don't need to redefine YYSTYPE at all on any of the three keywords.c > file. Looks cleaner overall. > > Hopefully this is the last version of this patch. FWIW gcc does this kind of trick all over the place. They have lists of various types of objects, not unlike our nodes and define them in .h files which contain _just_ the equivalent of PG_KEYWORD. Then they include those files in various places with the macro defined to do different things. So for example they define an enum, an array for external consumption, an array for internal consumption, and in many places even switch statements with trivial bits of code from the macro too. If we're going to go this route I think it does make sense to move the "const ScanKeyword ScanKeywords[] = {" preamble and to live with the PG_KEYWORD definition. Even if we're not planning to have any other kinds of macro definitions aside from ScanKeywords arrays today it would be nice to have the flexibility and in any case I don't really like the action-at-a-distance of having a macro definition in one place which depends on the definition in another place. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Tom Lane wrote: > Alvaro Herrera writes: > >> Seems doable. > > > Attached. > > The TWO_MEMBER_SCANKEYWORD business seems a bit dangerous --- if the > header file is read without having #defined that correctly, bad things > will happen. It might be better to leave that out, always define the > struct the same, and just have pg_dump define PG_KEYWORD to fill the > value field with zero. Given alignment considerations, you're not > saving any space by omitting the field anyhow. Fixed. I also added #include type.h to the ecpg keywords.c file, which means we don't need to redefine YYSTYPE at all on any of the three keywords.c file. Looks cleaner overall. Hopefully this is the last version of this patch. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Index: src/backend/parser/Makefile === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/Makefile,v retrieving revision 1.48 diff -c -p -r1.48 Makefile *** src/backend/parser/Makefile 4 Oct 2008 21:56:54 - 1.48 --- src/backend/parser/Makefile 4 Mar 2009 15:32:52 - *** override CPPFLAGS := -I$(srcdir) $(CPPFL *** 14,20 OBJS= analyze.o gram.o keywords.o parser.o parse_agg.o parse_cte.o parse_clause.o \ parse_expr.o parse_func.o parse_node.o parse_oper.o parse_relation.o \ ! parse_type.o parse_coerce.o parse_target.o parse_utilcmd.o scansup.o FLEXFLAGS = -CF --- 14,20 OBJS= analyze.o gram.o keywords.o parser.o parse_agg.o parse_cte.o parse_clause.o \ parse_expr.o parse_func.o parse_node.o parse_oper.o parse_relation.o \ ! parse_type.o parse_coerce.o parse_target.o parse_utilcmd.o scansup.o kwlookup.o FLEXFLAGS = -CF Index: src/backend/parser/gram.y === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/gram.y,v retrieving revision 2.659 diff -c -p -r2.659 gram.y *** src/backend/parser/gram.y 24 Feb 2009 10:06:33 - 2.659 --- src/backend/parser/gram.y 4 Mar 2009 17:04:39 - *** static TypeName *TableFuncTypeName(List *** 423,429 /* * If you make any token changes, update the keyword table in ! * parser/keywords.c and add new keywords to the appropriate one of * the reserved-or-not-so-reserved keyword lists, below; search * this file for "Name classification hierarchy". */ --- 423,429 /* * If you make any token changes, update the keyword table in ! * src/include/parser/kwlist.h and add new keywords to the appropriate one of * the reserved-or-not-so-reserved keyword lists, below; search * this file for "Name classification hierarchy". */ *** static TypeName *TableFuncTypeName(List *** 516,522 ZONE ! /* The grammar thinks these are keywords, but they are not in the keywords.c * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ --- 516,522 ZONE ! /* The grammar thinks these are keywords, but they are not in the kwlist.h * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ Index: src/backend/parser/keywords.c === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/keywords.c,v retrieving revision 1.210 diff -c -p -r1.210 keywords.c *** src/backend/parser/keywords.c 24 Feb 2009 10:06:33 - 1.210 --- src/backend/parser/keywords.c 4 Mar 2009 15:56:35 - *** *** 3,10 * keywords.c * lexical token lookup for key words in PostgreSQL * - * NB: This file is also used by pg_dump. - * * * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California --- 3,8 *** *** 15,503 * *- */ ! /* Use c.h so that this file can be built in either frontend or backend */ ! #include "c.h" ! ! #include ! ! /* ! * This macro definition overrides the YYSTYPE union definition in gram.h. ! * We don't need that struct in this file, and including the real definition ! * would require sucking in some backend-only include files. ! */ ! #define YYSTYPE int ! #include "parser/keywords.h" - #ifndef ECPG_COMPILE #include "parser/gram.h" - #else - #include "preproc.h" - #endif - - /* - * List of keyword (name, token-value, category) entries. - * - * !!WARNING!!: This list must be sorted by ASCII name, because binary - * search is used to locate entries. - */ - const ScanKeyword ScanKeywords[] = { - /* name, value, category */ - {"abort", ABORT_P, UNRESERVED_KEYWORD}, - {"absolute", ABSOLUTE_P, UNRESERVED_KEYWORD}, - {"access",
Re: [HACKERS] building pg_dump doesn't work
Zdenek Kotala wrote: > Dne 3.03.09 22:55, Tom Lane napsal(a): > >> >> One idea that comes to mind is to replace the entries like >> >> {"abort", ABORT_P, UNRESERVED_KEYWORD}, >> >> with macro calls >> >> PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD), >> >> and then the frontend build of the file could define the macro >> to ignore its second argument. > > It sounds good. Please have a look at the patch I just posted -- should be up in a few minutes in http://archives.postgresql.org/message-id/20090305131208.GA4087%40alvh.no-ip.org -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
Joshua Tolley wrote: > On Wed, Mar 04, 2009 at 10:28:42AM +0100, Magnus Hagander wrote: >> Joshua Tolley wrote: >>> On Wed, Mar 04, 2009 at 09:43:55AM +0100, Magnus Hagander wrote: So. I've updated the comment, and applied your patch. Thanks! >>> What would it take to get it applied to a few earlier versions as well? >> I guess you maintaining your own fork? ;-) >> >> >> Simply put, earlier versions threw away the contents of pg_hba and >> reloaded it completely. The support for keeping the old one around in >> case of syntax errors is new for 8.4. You'd basically require >> backpatching of large parts of that patch, and that's not going to happen. >> >> //Magnus > > Given that we ran into the problem in 8.3.6, how about something like > the attached to apply to it? Yeah, the big question is if we want to backport something like this at all... Thoughts? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Constraint exclusion extension
Hi, we have come across a theoretical problem with a GIS database, which I think worth discussing. The database table is partitioned, it's already larger than 30TB. The table is partitioned over the PostGIS && (overlaps) operator. However, when SELECTing from that table, it goes through all partitions. Example query is below: select asbinary(force_collection(force_2d(the_geom)),'NDR') from gll_h.parent as foo where the_geom && setsrid('BOX3D(3550500 5811500 0,3550600 5811600 0)'::BOX3D::geometry,31467) After a little thinking, I would say it's natural that it considers all partitions. If A is the "super bounding box" in the CHECK contraint, B is the value of "the_geom" and the query contains a geometry that overlaps B but doesn't overlap A, it still needs to check the subtable to get all correct records. We are thinking about the following: partition the database over the @ (contained operator) and add an extension to the CREATE OPERATOR syntax so it can set up a connection between two different operators, much like the COMMUTATOR is now. Consider the following: A is the "super bounding box" in the CHECK contraint, B is the value of "the_geom" (B contained by A) and the query checks whether a geometry C overlaps B. 1. "B contained by A" AND "C overlaps B" => "C overlaps A". 2. "B contained by A" AND "C doesn't overlap A" => "C doesn't overlap B" Extending CREATE OPERATOR with this deduction feature or the grammar with some other syntax, building a net of deduction between functions this way, constraint exclusion may work more efficiently. Comments? Thanks in advance, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
Dickson S. Guedes escreveu: 2009/3/4 André Volpato : I´m trying to install postgres 8.3.6 at AIX 5.3, and I got a declaration error that I presume its related to this [1] thread. Here´s some info: - Bladecenter IBM JS22 - PowerPC_POWER6 quad 4GHZ 64 bits - AIX 5.3.0 - Postgresql 8.3.6 - gcc version 4.2.0 - GNU Make 3.80 (...) Here: postg...@db01 $ id uid=204(postgres) gid=1(staff) postg...@db01 $ export CC="gcc" postg...@db01 $ export CFLAGS="-maix64" postg...@db01 $ export LDFLAGS="-Wl,-bbigtoc" postg...@db01 $ export OBJECT_MODE=64 postg...@db01 $ export AR="ar -X64" postg...@db01 $ ./configure --enable-integer-datetimes --prefix=/srv/postgresql/8.3.6 postg...@db01 $ make ... ... And all is done fine. Course, all above is for 64 bits compilling. How you are trying that? I cant access the box now, so I´ll definetly try this later. I try configuring with no options, and no flags where given at all. That was my first try to compile on AIX, after a 7 month delay from IBM... -- []´s, ACV
Re: [HACKERS] cbrt() broken in AIX
Tom Lane escreveu: =?ISO-8859-1?Q?Andr=E9_Volpato?= writes: First I tryed to configure with no options, and then make throw this: float.c:74: error: static declaration of 'cbrt' follows non-static declaration After some googling, I find Bruce´s path back on 7.4, that removed my_cbrt trick. So, I changed float.c to the old code (the whole HAVE_CBRT stuff), Instead, try putting back the "#define cbrt my_cbrt" line. You mean only this line ? What I have done is this : (I cant access the box right now, so I dont have diff output) float.c >From : > #ifndef HAVE_CBRT > static double cbrt(double x); > #endif /* HAVE_CBRT */ To: > #ifndef HAVE_CBRT > #define cbrt my_cbrt > static double cbrt(double x); > #else > #if !defined(nextstep) > extern double cbrt(double x); > #endif > #endif /* HAVE_CBRT */ And that give the Undefined symbol errors. Anyway, I´ll try putting back just the my_cbrt line. -- []´s, ACV
Re: [HACKERS] SIGHUP during recovery
Fujii Masao wrote: BTW, I found that backup.sgml still had the description of log_restartpoints. Here is the patch to remove it. Thanks, I had put that in the Open Items list so that we remember to do that before release. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
Simon Riggs wrote: On Thu, 2009-03-05 at 19:52 +0900, Fujii Masao wrote: Hi, On Thu, Mar 5, 2009 at 6:28 PM, Simon Riggs wrote: Should we reload recovery.conf also? I think no for now. At least the parameters which specify the recovery target should not be changed during recovery. Why not? I don't see either why it wouldn't work. As long as the new value is greater than the current point in recovery. BTW, we now have some extra safeguards (minRecoveryPoint) in place to prevent you from corrupting your database by moving recovery target backwards to a point earlier than an already recovery xlog record. That's not really that relevant to reloading recovery.conf on the fly, but it's worth noting. The documentation currently says about the settings in recovery.conf that "They cannot be changed once recovery has begun." That wording should probably be relaxed. On the other hand, restore_command maybe can be set safely, but I'm not sure if it's really useful at this time. Not sure changing those parameters would be a bad thing. Other parameters can be changed, why not those? It seems safe to me. It doesn't seem very useful, though. The only options left in recovery.conf are restore_command, and all the target-related options. If you want to change those, you can always stop the server, change the settings and restart; without hot standby there isn't any other backends active yet that would get upset about the shutdown. The main reason why reloading the main config file on SIGHUP is a good idea is that otherwise you get an inconsistency between the background writer and the startup process. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
On Thu, 2009-03-05 at 19:52 +0900, Fujii Masao wrote: > Hi, > > On Thu, Mar 5, 2009 at 6:28 PM, Simon Riggs wrote: > > Should we reload recovery.conf also? > > I think no for now. At least the parameters which specify the recovery target > should not be changed during recovery. Why not? > On the other hand, restore_command > maybe can be set safely, but I'm not sure if it's really useful at this time. Not sure changing those parameters would be a bad thing. Other parameters can be changed, why not those? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
Hi, On Thu, Mar 5, 2009 at 6:28 PM, Simon Riggs wrote: > Should we reload recovery.conf also? I think no for now. At least the parameters which specify the recovery target should not be changed during recovery. On the other hand, restore_command maybe can be set safely, but I'm not sure if it's really useful at this time. Or, upcoming HS needs such capability? BTW, I found that backup.sgml still had the description of log_restartpoints. Here is the patch to remove it. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center remove_log_restartpoints_doc.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Dne 3.03.09 22:55, Tom Lane napsal(a): One idea that comes to mind is to replace the entries like {"abort", ABORT_P, UNRESERVED_KEYWORD}, with macro calls PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD), and then the frontend build of the file could define the macro to ignore its second argument. It sounds good. The way we do it now seems to have other disadvantages too: we are incorporating a backend .o file into pg_dump as-is, which would lead to large problems if there were differences in say the compiler flags needed. In fact, I thought Zdenek had been working on decoupling that sort of thing, so I'm a bit surprised it's still like this at all. Yeah, it is still on my TODO list. There is still problem with pg_resetxlog which needs lot of internals headers. :( Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
On Wed, 2009-03-04 at 15:58 +0200, Heikki Linnakangas wrote: > Fujii Masao wrote: > > Currently, the startup process ignores SIGHUP. > > > > The attached patch allows the startup process to re-read config file: > > when SIGHUP arrives, the startup process also receives the signal > > from postmaster and reload the settings in main redo apply loop. > > Obviously, this is useful to change the parameters which the startup > > process may use (e.g. log_line_prefix, log_checkpoints). > > Thanks, committed. > > The fact that bgwriter can run simultaneously with the startup process > makes this more important than before. Otherwise if you change something > like log_line_prefix, bgwriter will use the new setting but startup > process will not. Should we reload recovery.conf also? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
Peter Eisentraut wrote: > I have been thinking about this for a great while now. I am not yet > comfortable with how we manage the access rights here. We have > restricted access to the user mappings catalog to hide passwords, but it > is not entirely clear why a password must be stored in a user mapping. > It could also be stored with a server, if we only want to use one global > connection for everybody. > Hmm, in this case one would probably create a PUBLIC user mapping and store the password there. But indeed, there could be other aspects of the server that need to be kept secret. > I think the proper way to handle it might be to introduce a new > privilege type -- call it SELECT if you like -- that determines > specifically whether you can *see* the options of a foreign-data > wrapper, foreign server, or user mapping, respectively. How about providing an optional masking function for the foreign data wrapper. The function would accept the generic options array and remove/mask any undesired options. Ordinary users would access the catalogs by views, and only see the filtered or masked options. The owner and superuser would still have to get the full options though. Just an idea. regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)
Heikki Linnakangas wrote: KaiGai Kohei wrote: The other one is it has two kind of reader permissions ("select" and "use"). The "select" permission is applied when user tries to read tables/columns and its contents are returned to the user. The "use" permission is applied when user tries to read table/columns, but its contents are consumed internally (not returned to user directly). For example: SELECT a, b FROM t WHERE b > 10 and c = 'aaa'; In this case, db_column:{select} permission is applied on "t.a". db_column:{select use} permission is applied on "t.b". db_column:{use} permission is applied on "t.c". db_table:{select use} permission is applied on "t" However, I don't absolutely oppose to integrate them into a single reader "select" permission, because it was originally a single permission, then "use" is added. If you have "use" permisson on c, you can easily use it to find out the exact value. Just do queries like "SELECT 'foo' FROM t WHERE b > 10 AND c = 'aaa' AND c BETWEEN 1 AND 1000" repeatedly with different ranges to zoom into the exact value. So I think separating those two permissions is a mistake, Hmm. At least, I can agree to integrate these two permissions into a single "select". It is originally upper compatible to "use". Please note that user's privileges are not limited to create/alter/drop them. One sensitive permission is "db_procedure:{install}". It is checked when user defined functions are set up as a function internally invoked. For example, "pg_conversion.conproc" is internally invoked to translate a text, but it does not check pg_proc_aclcheck() in runtime. We consider all user defined functions should be checked either of: - "db_procedure:{execute}" permission for the client in runtime or - "db_procedure:{install}" permission for the DBA on installation time Needless to say, "{install}" is more sensitive permission because it means anyones to invoke it implicitly. So, the default policy only allows it on functions defined by DBA, but the "execute" permission is allowed normal users to invoke functions defined by himself. Hmm. We normally rely on the fact that a conversion function needs to be a C-function, and because only superusers can create C-functions we have assumed that they're safe to call. Which was actually not true until recently, when we added checks into all the conversion functions to check that the source and target encoding of the strings passed as arguments match the ones specified in the CREATE CONVERSION command. There has been talks of making CREATE CONVERSION superuser-only, so we could easily just do that. Can you give some other examples of where the "install" permission is used? Because SE-PostgreSQL works orthogonally to the existing access controls, so we need to consider two cases. a) A superuser connected from unprivileged domain (like: user_t, httpd_t) b) A superuser connected from privileged domain (like: sysadm_t, unconfined_t) The superuser is a concept of PostgreSQL, and the domain is a concept of SELinux. It seems to me you assumes the b) case. The a) case should be focused on here. In the a) case, SE-PostgreSQL does not prevent to create C-function (as far as shared library has an appropriate label: "lib_t"), and newly created functions are labeled as "unpriv-user defined functions" which depends on the domain. It allows unpriv-domains to invoke functions defined by himself, but does not allow to "install" as a conversion and others, because it can be implicitly used by other domains. NOTE: unprivileged domain cannot create files labeled as "lib_t" on the operating system, so all they can do is to load libraries already set up. Our assumption is a client connected from user_t or httpd_t is suspicious, even if they logged in as a superuser, so SE-PostgreSQL applies additional checks. But if I've understood correctly, one goal is to restrict the actions of superusers as well. Is there something to disallow superusers from creating C-functions? If yes, isn't that enough protection from things like the conversion functions? Please note that SE-PostgreSQL focuses on the domain a client connected from, not attributes of database user. (Needless to say, vanilla PostgreSQL concurrently prevents C-functions by normal database users.) The conversion is an example of "install" permissions. The list of functions to be checked are here. http://code.google.com/p/sepgsql/source/browse/trunk/sepgsql/src/backend/security/sepgsql/hooks.c#446 Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use array in a dynamic statement
Hello you can't to use parameters inside literal. There hasn't any sense (to 8.3, 8.4 will support USING). you have to use serialisation to string and quoting. some like CREATE OR REPLACE FUNCTION foo(int[]) RETURNS SETOF int AS $$ DECLARE r record; BEGIN FOR r IN EXECUTE 'SELECT (' || quote_literal($1::text) || '::int[])[i] AS x FROM generate_series(1, array_upper(' || quote_literal($1::text) || '::int[],1)) g(i)' LOOP RETURN NEXT r.x; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(ARRAY[1,2,3]); CREATE OR REPLACE FUNCTION foo84(int[]) RETURNS SETOF int AS $$ DECLARE r record; BEGIN FOR r IN EXECUTE 'SELECT $1[i] AS x FROM generate_series(1, array_upper($1,1)) g(i)' USING $1 LOOP RETURN NEXT r.x; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo84(ARRAY[1,2,3]); regards Pavel Stehule 2009/3/5 Sophie Yang : > > Hi, > > I am trying to implement a PL/PgSQL function as following: > CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR) > RETURNS varchar[] > AS $$ > DECLARE > result varchar[]; > BEGIN > > EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1), > array_upper($1,1)) AS s(i), ' > ||tbl_name||' t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER > BY length(t.idx))' > INTO result; > > RETURN result; > END; > $$ LANGUAGE plpgsql; > > I got an error "ERROR: there is no parameter $1" when I test the function > with: > select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170, > 1}}'::int[][], 'd_tree_1'); > > The error is understandable, but my question is how to supply the int[][] > array into the dynamic SQL? > > To help understand the dynamic statement, the structure of d_tree_1 is (rid, > rtid, idx). The PK is (rid, rtid) pair. > > If the tbl_name is fixed, the following function works well: > CREATE OR REPLACE FUNCTION sort_by_d_idx2 (nids INT[][]) > RETURNS varchar[] > LANGUAGE SQL > AS $$ > SELECT ARRAY( > SELECT t.idx > FROM > generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), > d_tree_1 t > WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid > ORDER BY length(t.idx) > ); > $$; > > Unfortunately, the tbl_name is determined at query time. > > Please help. > > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers