Re: [HACKERS] Row Level Security Documentation
On Thu, Jul 13, 2017 at 5:49 AM, Fabien COELHO wrote: > > Hello Rod, > > This version of the table attempts to stipulate which section of the >> process the rule applies to. >> > > The table should be referenced from the description, something like "Table > xxx summarizes the ..." > Added the below which seemed consistent with other "see something else" messages. A summary of the application of policies to a command is found in . > ISTM that it would be clearer to split the Policy column into "FOR xxx > ..." and "USING" or "WITH CHECK", and to merge the rows which have the same > "FOR xxx ..." contents, something like: > >POLICY | > ---++- > | USING | ... > FOR ALL ...++- > | WITH CHECK | ... > ---++- > FOR SELECT ... | USING | ... > > So that it is clear that only ALL & UPDATE can get both USING & WITH > CHECK. This can be done with "morerows=1" on an entry so that it spans more > rows. > Done. I couldn't figure out a morecols=1 equivalent to keep everything under the Policy heading without a full colspec. > For empty cells, maybe a dash would be clearer. Not sure. Looked cluttered to me. Tried N/A first which was even worse. -- Rod Taylor diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index c0dfe1ea4b..52a868e65d 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -94,6 +94,11 @@ CREATE POLICY name ON default deny policy is assumed, so that no rows will be visible or updatable. + + + A summary of the application of policies to a command is found + in . + @@ -389,6 +394,80 @@ CREATE POLICY name ON + + Policies Applied During Statement + + + + + + + + + + Policy + SELECT + INSERT + UPDATE + DELETE + + + + + FOR ALL ... + USING + WHERE clause + RETURNING clause + WHERE and RETURNING clause + WHERE and RETURNING clause + + + WITH CHECK + + new tuple + new tuple + new tuple + + + FOR SELECT ... USING + WHERE clause + RETURNING clause + WHERE and RETURNING clause + WHERE and RETURNING clause + + + FOR INSERT ... WITH CHECK + + new tuple + + + + + FOR UPDATE ... + USING + + + WHERE clause + + + + WITH CHECK + + + new tuple + + + + FOR DELETE ... USING + + + + WHERE clause + + + + + -- 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] RLS in CTE incorrect permission failure
On Wed, Jun 21, 2017 at 7:46 PM, Tom Lane wrote: > Rod Taylor writes: > > In the attached script, the second insert into t2 (as part of the CTE) > > should succeed. > > No, I don't think so. You declared the check function as STABLE which > means it is confined to seeing the same snapshot as the surrounding query. > So it can't see anything inserted by that query. > > Possibly it'd work as you wish with a VOLATILE function. > Indeed, that works as expected. Sorry for the noise. -- Rod Taylor
[HACKERS] RLS in CTE incorrect permission failure
In the attached script, the second insert into t2 (as part of the CTE) should succeed. My actual use case isn't much more complex; the function is used primarily to allow peaking at columns that the function definer has access to but a typical user does not. Function also makes it easy to copy this policy to a number of structures. The function within the policy doesn't seem to be able to see records inserted by earlier statements in the CTE. Perhaps this is as simple as adding a command counter increment in the right place? Fails in 9.5.7 and HEAD. -- Rod Taylor cte_rls_fail.sql Description: application/sql -- 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] Row Level Security Documentation
Of course, better thoughts appear immediately after hitting the send button. This version of the table attempts to stipulate which section of the process the rule applies to. On Thu, May 11, 2017 at 8:40 PM, Rod Taylor wrote: > I think the biggest piece missing is something to summarize the giant > blocks of text. > > Attached is a table that has commands and policy types, and a "yes" if it > applies. > > -- > Rod Taylor > -- Rod Taylor diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3b24e5e95e..4c997a956d 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -389,6 +389,72 @@ CREATE POLICY name ON + + Policies Applied During Statement + + + + Policy + SELECT + INSERT + UPDATE + DELETE + + + + + FOR ALL ... USING + WHERE clause + RETURNING clause + WHERE and RETURNING clause + WHERE and RETURNING clause + + + FOR ALL ... WITH CHECK + + new tuple + new tuple + new tuple + + + FOR SELECT ... USING + WHERE clause + RETURNING clause + WHERE and RETURNING clause + WHERE and RETURNING clause + + + FOR INSERT ... WITH CHECK + + new tuple + + + + + FOR UPDATE ... USING + + + WHERE clause + + + + FOR UPDATE ... WITH CHECK + + + new tuple + + + + FOR DELETE ... USING + + + + WHERE clause + + + + + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Row Level Security Documentation
I think the biggest piece missing is something to summarize the giant blocks of text. Attached is a table that has commands and policy types, and a "yes" if it applies. -- Rod Taylor diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3b24e5e95e..c737f9e884 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -389,6 +389,96 @@ CREATE POLICY name ON + + Policies Applied During Statement + + + + Policy + SELECT + INSERT + INSERT RETURNING + UPDATE WHERE + UPDATE RETURNING + DELETE WHERE + DELETE RETURNING + + + + + FOR ALL ... USING + yes + + + yes + yes + yes + yes + + + FOR ALL ... WITH CHECK + + yes + yes + yes + yes + + + + + FOR SELECT ... USING + yes + + yes + yes + yes + yes + yes + + + FOR INSERT ... WITH CHECK + + yes + yes + + + + + + + FOR UPDATE ... USING + + + + yes + yes + + + + + FOR UPDATE ... WITH CHECK + + + + yes + yes + + + + + FOR DELETE ... USING + + + + + + yes + yes + + + + + -- 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] Row Level Security UPDATE Confusion
Hmm. UPDATE seems to work as described (unable to create records you cannot select); both the single rule version and multi-rule version seem to work the same. This combination works too though it seems funny that UPDATE can create an entry it cannot reverse. I can set the value to 100 (going to -1 fails) but the UPDATE cannot see the record to set it back. I can see use cases for it, for example you might be able to promote someone to manager but not demote a manager to front-desk. We also allow INSERT on tables you cannot delete from, so it's not inconsistent. CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0); CREATE POLICY split_update ON t FOR UPDATE TO split USING (value < 10) WITH CHECK (value > 2); SET session authorization split; update t set value = 100 where value = 4; -- 1 record changed update t set value = 5 where value = 100; -- 0 records changed However, despite INSERT also functioning the same for both styles of commands it's definitely not obeying the `cannot give away records` rule: CREATE USER simple; CREATE USER split; CREATE TABLE t(value int); grant select, update, insert, delete on table t to simple, split; INSERT INTO t values (1), (2); ALTER TABLE t ENABLE ROW LEVEL SECURITY; CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true); CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0); CREATE POLICY split_insert ON t FOR INSERT TO split WITH CHECK (true); SET session authorization simple; INSERT INTO t VALUES (3), (-3); SELECT * FROM t; value --- 1 2 3 (3 rows) SET session authorization split; INSERT INTO t VALUES (4), (-4); SELECT * FROM t; value --- 1 2 3 4 (4 rows) SET session authorization default; SELECT * FROM t; value --- 1 2 3 -3 4 -4 (6 rows) regards, Rod On Fri, May 5, 2017 at 12:10 PM, Stephen Frost wrote: > Rod, Robert, > > * Robert Haas (robertmh...@gmail.com) wrote: > > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost > wrote: > > > I agreed already up-thread that there's an issue there and will be > > > looking to fix it. That comment was simply replying to Rod's point > that > > > the documentation could also be improved. > > > > OK, thanks. The wrap for the next set of minor releases is, according > > to my understanding, scheduled for Monday, so you'd better jump on > > this soon if you're hoping to get a fix out this time around. > > The attached patch against master fixes this issue. Rod, if you get a > chance, would be great for you to check that you no longer see a > difference between the single ALL policy and the split SELECT/UPDATE > policies. > > Thanks! > > Stephen > -- Rod Taylor
Re: [HACKERS] Row Level Security UPDATE Confusion
On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost wrote: > Rod, > > * Rod Taylor (rod.tay...@gmail.com) wrote: > > My actual use-case involves a range. Most users can see and manipulate > the > > record when CURRENT_TIMESTAMP is within active_period. Some users > > (staff/account admins) can see recently dead records too. And a 3rd group > > (senior staff) have no time restriction, though there are a few customers > > they cannot see due to their information being a touch more sensitive. > > I've simplified the below rules to just deal with active_period and the > > majority of user view (@> CURRENT_TIMESTAMP). > > Interesting. > > > NOTE: the active_period range is '[)' by default, so records with > upper() = > > CURRENT_TIMESTAMP are not visible with @> CURRENT_TIMESTAMP restriction. > > Is that really what you intend/want though? For records with > upper() = CURRENT_TIMESTAMP to not be visible? You are able to change > the range returned from tstzrange by specifying what you want, eg: > Yeah, think of it like a delete. Once a record is deleted you want it to disappear. From the users point of view, who doesn't have time-travel privileges, an UPDATE to upper() = CURRENT_TIMESTAMP should disappear from any actions that take place later in the transaction. A more common way of implementing this is an archive table. Have a DELETE trigger and shuffle the record to another storage area but with many dependent tuples via foreign key this can be very time consuming. Flipping a time period is consistently fast with the caveat that SELECT pays a price. If you decide Pg shouldn't allow a user to make a tuple disappear, I would probably make a DO INSTEAD SECURITY DEFINER function that triggers on DELETE for that role only and changes the time range. Reality is after about 1 week for customers to contact their account administrator and say "I accidentally deleted X" it would likely be moved to an archive structure. select tstzrange(current_timestamp, current_timestamp, '[]'); > > > CREATE A TABLE t (id integer, active_period tstzrange NOT NULL DEFAULT > > tstzrange(current_timestamp, NULL)); > > Why NULL instead of 'infinity'...? > Diskspace. NULL works (almost) the same as infinity but the storage is quite a bit smaller. > > > -- Disallowed due to hide_old_select policy. > > UPDATE t SET active_period = tstzrange(lower(active_period), > > CURRENT_TIMESTAMP); > > Guess I'm still trying to figure out if you really intend for this to > make the records invisible to the 'most users' case. > Yep. It's equivalent to a DELETE or DEACTIVATE. RLS may not be the right facility but it was very close to working exactly the way I wanted in FOR ALL mode. -- Rod Taylor
Re: [HACKERS] Row Level Security UPDATE Confusion
On Fri, Apr 14, 2017 at 7:41 AM, Rod Taylor wrote: > > > > On Thu, Apr 13, 2017 at 5:31 PM, Stephen Frost wrote: > >> Rod, all, >> >> * Joe Conway (m...@joeconway.com) wrote: >> > On 04/13/2017 01:31 PM, Stephen Frost wrote: >> > > * Robert Haas (robertmh...@gmail.com) wrote: >> > >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor >> wrote: >> > >> > I'm a little confused on why a SELECT policy fires against the NEW >> record >> > >> > for an UPDATE when using multiple FOR policies. The ALL policy >> doesn't seem >> > >> > to have that restriction. >> > >> >> > >> My guess is that you have found a bug. >> > > >> > > Indeed. Joe's been looking into it and I'm hoping to find some time >> to >> > > dig into it shortly. >> > >> > >> CREATE POLICY split_select ON t FOR SELECT TO split >> > >> USING (value > 0); >> > >> CREATE POLICY split_update ON t FOR UPDATE TO split >> > >> USING (true) WITH CHECK (true); >> > >> > Yes -- from what I can see in gdb: >> >> Actually, looking at this again, the complaint appears to be that you >> can't "give away" records. That was a topic of much discussion and I'm >> reasonably sure that was what we ended up deciding made the most sense. >> You have to be able to see records to be able to update them (you can't >> update records you can't see), and you have to be able to see the result >> of your update. I don't doubt that we could improve the documentation >> around this (and apparently the code comments, according to Joe..). >> >> > Then there is a bug in the simpler statement which happily lets you give > away records. > > CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK > (true); > > SET session authorization simple; > SELECT * FROM t; > UPDATE t SET value = value * -1 WHERE value = 1; > -- No error and value is -1 at the end. > My actual use-case involves a range. Most users can see and manipulate the record when CURRENT_TIMESTAMP is within active_period. Some users (staff/account admins) can see recently dead records too. And a 3rd group (senior staff) have no time restriction, though there are a few customers they cannot see due to their information being a touch more sensitive. I've simplified the below rules to just deal with active_period and the majority of user view (@> CURRENT_TIMESTAMP). NOTE: the active_period range is '[)' by default, so records with upper() = CURRENT_TIMESTAMP are not visible with @> CURRENT_TIMESTAMP restriction. CREATE A TABLE t (id integer, active_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)); The below policy is allowed but requires that 1ms slop to accommodate the wi Updated record invisible to USING but requires a trigger to enforce specific upper and starting values. I have a trigger enforcing specific upper/lower values for the range for specific ROLEs. So I had the thought that I might move ROLE specific trigger logic into the RLS mechanism. CREATE POLICY hide_old ON t TO s; USING ( active_period @> CURRENT_TIMESTAMP) WITH CHECK ( active_period && tstzrange(current_timestamp - interval '0.001 seconds', current_timestamp, '[]')); -- This is effectively a delete for the above policy. It becomes immediately invisible due to USING restriction. UPDATE t SET active_period = tstzrange(lower(active_period), CURRENT_TIMESTAMP); SELECT count(*) FROM t; -- 0 records I tried to tighten the above rules, so INSERT must have upper of NULL and UPDATE must set upper to exactly CURRENT_TIMESTAMP. Clearly I can achieve this using triggers for enforcement but I tried to abuse RLS instead because it is a role specific restriction. I was surprised when hide_old_select->USING was preventing the UPDATE when the simple single policy version let it through. CREATE POLICY hide_old_select ON t FOR SELECT TO s USING ( active_period @> CURRENT_TIMESTAMP); CREATE POLICY hide_old_insert ON t FOR INSERT to s WITH CHECK ( lower(active_period) = CURRENT_TIMESTAMP AND upper(active_period) IS NULL); CREATE POLICY hide_old_update ON t FOR UPDATE TO s USING ( active_period @> CURRENT_TIMESTAMP) WITH CHECK ( upper(active_period) = CURRENT_TIMESTAMP); -- Disallowed due to hide_old_select policy. UPDATE t SET active_period = tstzrange(lower(active_period), CURRENT_TIMESTAMP); I'm happy to help with testing and documentation but first I need to understand what the intended functionality was. Right now it seems inconsistent between the simple single policy version and the multi policy version; the docs imply the single policy version is correct (it only seems to mention SELECT checks on RETURNING clauses). -- Rod Taylor
Re: [HACKERS] Row Level Security UPDATE Confusion
On Thu, Apr 13, 2017 at 5:31 PM, Stephen Frost wrote: > Rod, all, > > * Joe Conway (m...@joeconway.com) wrote: > > On 04/13/2017 01:31 PM, Stephen Frost wrote: > > > * Robert Haas (robertmh...@gmail.com) wrote: > > >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor > wrote: > > >> > I'm a little confused on why a SELECT policy fires against the NEW > record > > >> > for an UPDATE when using multiple FOR policies. The ALL policy > doesn't seem > > >> > to have that restriction. > > >> > > >> My guess is that you have found a bug. > > > > > > Indeed. Joe's been looking into it and I'm hoping to find some time to > > > dig into it shortly. > > > > >> CREATE POLICY split_select ON t FOR SELECT TO split > > >> USING (value > 0); > > >> CREATE POLICY split_update ON t FOR UPDATE TO split > > >> USING (true) WITH CHECK (true); > > > > Yes -- from what I can see in gdb: > > Actually, looking at this again, the complaint appears to be that you > can't "give away" records. That was a topic of much discussion and I'm > reasonably sure that was what we ended up deciding made the most sense. > You have to be able to see records to be able to update them (you can't > update records you can't see), and you have to be able to see the result > of your update. I don't doubt that we could improve the documentation > around this (and apparently the code comments, according to Joe..). > > Then there is a bug in the simpler statement which happily lets you give away records. CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true); SET session authorization simple; SELECT * FROM t; UPDATE t SET value = value * -1 WHERE value = 1; -- No error and value is -1 at the end. -- Rod Taylor
[HACKERS] Row Level Security UPDATE Confusion
I'm a little confused on why a SELECT policy fires against the NEW record for an UPDATE when using multiple FOR policies. The ALL policy doesn't seem to have that restriction. DROP TABLE IF EXISTS t; CREATE USER simple; CREATE USER split; CREATE TABLE t(value int); grant select, update on table t to simple, split; INSERT INTO t values (1), (2); ALTER TABLE t ENABLE ROW LEVEL SECURITY; CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true); CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0); CREATE POLICY split_update ON t FOR UPDATE TO split USING (true) WITH CHECK (true); SET session authorization simple; SELECT * FROM t; UPDATE t SET value = value * -1 WHERE value = 1; SET session authorization split; SELECT * FROM t; UPDATE t SET value = value * -1 WHERE value = 2; /* UPDATE fails with below error: psql:/tmp/t.sql:24: ERROR: 42501: new row violates row-level security policy for table "t" LOCATION: ExecWithCheckOptions, execMain.c:2045 */ SET session authorization default; SELECT * FROM t; This seems consistent in both Pg 9.5 and upcoming Pg 10. -- Rod Taylor
Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE
On Thu, Feb 2, 2017 at 11:40 AM, Alvaro Herrera wrote: > Pavel Stehule wrote: > > > Identification of unjoined tables should be very useful - but it is far > to > > original proposal - so it can be solved separately. > > > > This patch is simple - and usually we prefer more simple patches than one > > bigger. > > > > Better to enhance this feature step by step. > > Agreed -- IMO this is a reasonable first step, except that I would > rename the proposed extension so that it doesn't focus solely on the > first step. I'd pick a name that suggests that various kinds of checks > are applied to queries, so "require_where" would be only one of various > options that can be enabled. > A general SQL-Critic would be a very welcome extension.
Re: [HACKERS] Design for In-Core Logical Replication
On Wed, Jul 20, 2016 at 4:08 AM, Simon Riggs wrote: > > > And on Subscriber database: > > CREATE SUBSCRIPTION mysub WITH CONNECTION dbname=foo host=bar > user=repuser PUBLICATION mypub; > > > > The above will start the replication process which synchronizes the > initial table contents of users and > departments tables and then starts replicating > incremental changes to those tables. > > > > I think it's important for communication channels to be defined separately from the subscriptions. If I have nodes 1/2 + 3/4 which operate in pairs, I don't really want to have to have a script reconfigure replication on 3/4 every-time we do maintenance on 1 or 2. 3/4 need to know they subscribe to mypub and that they have connections to machine 1 and machine 2. The replication system should be able to figure out which (of 1/2) has the most recently available data. So, I'd rather have: CREATE CONNECTION machine1; CREATE CONNECTION machine2; CREATE SUBSCRIPTION TO PUBLICATION mypub; Notice I explicitly did not tell it how to get the publication but if we did have a preference the DNS weighting model might be appropriate. I'm not certain the subscription needs to be named. IMO, a publication should have the same properties on all nodes (so any node may become the primary source). If a subscriber needs different behaviour for a publication, it should be created as a different publication. Documenting that ThisPub is different from ThatPub is easier than documenting that ThisPub on node 1/2/4 is different from ThisPub on node 7/8, except Node 7 is temporarily on Node 4 too (database X instead of database Y) due to that power problem. Clearly this is advanced. An initial implementation may only allow mypub from a single connection. I also suspect multiple publications will be normal even if only 2 nodes. Old slow moving data almost always got different treatment than fast-moving data; even if only defining which set needs to hit the other node first and which set can trickle through later. regards, Rod Taylor
Re: [HACKERS] array of domain types
On Thu, Jun 2, 2016 at 10:42 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > On 02.06.2016 17:22, Tom Lane wrote: > >> konstantin knizhnik writes: >> >>> Attached please find patch for DefineDomain function. >>> >> You didn't attach the patch, >> > > Sorry, but I did attached the patch - I see the attachment in my mail > received from the group. > Multidimensional arrays work fine: > > knizhnik=# SELECT '{{14},{20}}'::teenager[][]; > ERROR: value for domain teenager violates check constraint > "teenager_check" > LINE 1: SELECT '{{14},{20}}'::teenager[][]; >^ > knizhnik=# SELECT '{{14},{19}}'::teenager[][]; > teenager > - > {{14},{19}} > (1 row) > > knizhnik=# SELECT ('{{14},{19}}'::teenager[][])[1][1]; > teenager > -- >14 > (1 row) > > > Domain of array of domain also works: > > I applied the domain.patch from above on HEAD, and all I get is cache lookup failures. The type_sanity regression test fails too. postgres=# CREATE DOMAIN teenager AS int CHECK (VALUE BETWEEN 13 AND 20); CREATE DOMAIN postgres=# CREATE DOMAIN teenager_groups AS teenager[]; CREATE DOMAIN postgres=# CREATE TABLE x (col teenager_groups); ERROR: cache lookup failed for type 0 Anyway, if that worked for me I would have done this which I expect will succeed when it shouldn't. INSERT INTO x VALUES (ARRAY[13,14,20]); ALTER DOMAIN teenager DROP CONSTRAINT teenager_check; ALTER DOMAIN teenager ADD CHECK (VALUE BETWEEN 13 AND 19);
Re: [HACKERS] LOCK TABLE .. DEFERRABLE
On Tue, Apr 5, 2016 at 1:10 PM, Simon Riggs wrote: > If a lock is successfully obtained on one table, but not on all tables, it >> releases that lock and will retry to get them as a group in the future. >> Since inheritance acts as a group of tables (top + recursive cascade to >> children), this implementation is necessary even if only a single table is >> specified in the command. >> > > I'd prefer to see this as a lock wait mode where it sits in the normal > lock queue BUT other lock requestors are allowed to queue jump past it. > That should be just a few lines changed in the lock conflict checker and > some sleight of hand in the lock queue code. > > That way we avoid the busy-wait loop and multiple DEFERRABLE lock waiters > queue up normally. > Yeah, that would be better. I can see how to handle a single structure in that way but I'm not at all certain how to handle multiple tables and inheritance is multiple tables even with a single command. X1 inherits from X There is a long-running task on X1. Someone requests LOCK TABLE X IN ACCESS EXCLUSIVE MODE WAIT PATIENTLY. Internally this also grabs X1. The lock on X might be granted immediately and now blocks all other access to that table. There would need be a Locking Group kind of thing so various LockTags are treated as a single entity to grant them simultaneously. That seems pretty invasive; at least I don't see anything like that today.
[HACKERS] LOCK TABLE .. DEFERRABLE
The intention of this feature is to give the ability to slip into a normal workload for non-urgent maintenance work. In essence, instead of lock waiters being in a Queue, DEFERRABLE causes the current lock statement to always be last. It was discussed at last years pgCon as useful for replication tools adding/removing triggers. I've also seen more than one plpgsql loop using subtransactions and LOCK TABLE .. NOWAIT to achieve a similar effect. IMO, it's much cleaner built in. If a lock is successfully obtained on one table, but not on all tables, it releases that lock and will retry to get them as a group in the future. Since inheritance acts as a group of tables (top + recursive cascade to children), this implementation is necessary even if only a single table is specified in the command. Like various CONCURRENT commands, it waits on a set of transactions which were found to be blocking it. This puts it into the "waiting" state and allows isolation testing to work as expected. I started with a simple loop with a timer (and a GUC) but it didn't feel right without pg_stat_activity showing the waiting state. statement_timeout is suggested for a time restriction. Possibly Ugly stuff: SetLocktagRelationOid() no longer static inline. Better option? My C foo isn't all that it should be. Lock Table allows locking shared tables so I can't just assume MyDatabaseId is sufficient for the lock tag. Return value InvalidOid in RangeVarGetRelidExtended() can now appear in 2 different situations; relation missing if missing_ok enabled and relation unlockable if LockWaitPolicy LockWaitNonBlock. No callers currently use both of these options at this time. LockTableRecurse() returns the OID of the relation it could not lock in order to wait on the processes holding those locks. It also keeps a list of everything it did lock so they can be unlocked if necessary. I'll add it to the open November commitfest. regards, Rod Taylor diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index b946eab..e852f1d 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ] +LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT | DEFERRABLE ] where lockmode is one of: @@ -39,7 +39,23 @@ LOCK [ TABLE ] [ ONLY ] name [ * ] NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an - error is emitted. Once obtained, the lock is held for the + error is emitted. + + + + If DEFERRABLE is specified, + LOCK TABLE will wait without blocking for the + duration of + for all locks to become available. If all locks cannot be obtained + simultaneously before the timeout then none of the structures + will be locked and an error is emitted. Since it is non-blocking, + other transactions may obtain locks freely and may cause the + required wait time to be infinite. Use statement_timeout + for to restrict the wait time. + + + + Once obtained, the lock is held for the remainder of the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 34ba385..4259072 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -4865,6 +4865,9 @@ l3: RelationGetRelationName(relation; break; + case LockWaitNonBlock: + elog(ERROR, "unsupported lock wait_policy LockWaitNonBlock"); + break; } /* @@ -4902,6 +4905,9 @@ l3: errmsg("could not obtain lock on row in relation \"%s\"", RelationGetRelationName(relation; break; + case LockWaitNonBlock: + elog(ERROR, "unsupported lock wait_policy LockWaitNonBlock"); + break; } } @@ -5125,6 +5131,9 @@ heap_acquire_tuplock(Relation relation, ItemPointer tid, LockTupleMode mode, errmsg("could not obtain lock on row in relation \"%s\"", RelationGetRelationName(relation; break; + case LockWaitNonBlock: + elog(ERROR, "unsupported lock wait_policy LockWaitNonBlock"); + break; } *have_tuple_lock = true; diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 446b2ac..a0c4e56 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -46,6 +46,7 @@ #include "nodes/makefuncs.h" #include "parser/parse_func.h" #include "storage/ipc.h" +#include "storage/lock.h" #include "storage/lmgr.h" #include "storage/sinval.h" #include "utils/acl.h" @@ -223,14 +224,19 @@ Datum pg_is_other
Re: [HACKERS][PROPOSAL] Covering + unique indexes.
On Tue, Sep 15, 2015 at 12:57 PM, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote: > > Proposal Clarification. > I see that discussion become too complicated. So, I'd like to clarify > what we are talking about. > > We are discussing 2 different improvements of index. > The one is "partially unique index" and the other "index with included > columns". > Let's look at example. > > - We have a table tbl(f1, f2, f3, f4). > - We want to have an unique index on (f1,f2). > - We want to have an index on (f1, f2, f3) which allow us to use index for > complex "where" clauses. > Can someone write a query where F3 being ordered is a contribution? If F1 and F2 are unique, adding F3 to a where or order by clause doesn't seem to contribute anything. -- Already fully ordered by F1,F2 SELECT ... ORDER BY F1, F2, F3; -- F3 isn't in a known order without specifying F2 SELECT ... WHERE F1 = ? ORDER BY F1, F3; -- Index resolves to a single record; nothing to order SELECT ... WHERE F1 = ? AND F2 = ? ORDER BY F3; -- Without a where clause, the index isn't helpful unless F3 is the first column SELECT ... ORDER BY F3; What is it that I'm missing?
Re: [HACKERS] Allow "snapshot too old" error, to prevent bloat
On Wed, Feb 18, 2015 at 4:57 PM, Kevin Grittner wrote: > >> But max_standby_streaming_delay, max_standby_archive_delay and > >> hot_standby_feedback are among the most frequent triggers for > >> questions and complaints that I/we see. > >> > > Agreed. > > And a really bad one used to be vacuum_defer_cleanup_age, because > > of confusing units amongst other things. Which in terms seems > > fairly close to Kevins suggestions, unfortunately. > > Particularly my initial suggestion, which was to base snapshot > "age" it on the number of transaction IDs assigned. Does this look > any better to you if it is something that can be set to '20min' or > '1h'? Just to restate, that would not automatically cancel the > snapshots past that age; it would allow vacuum of any tuples which > became "dead" that long ago, and would cause a "snapshot too old" > message for any read of a page modified more than that long ago > using a snapshot which was older than that. > > I like this thought. One of the first things I do in a new Pg environment is setup a cronjob that watches pg_stat_activity and terminates most backends over N minutes in age (about 5x the length of normal work) with an exception for a handful of accounts doing backups and other maintenance operations. This prevents a stuck client from jamming up the database. Would pg_dump be able to opt-out of such a restriction? regards, Rod
Re: [HACKERS] Column Redaction
On Fri, Oct 10, 2014 at 10:56 AM, Stephen Frost wrote: > * Thom Brown (t...@linux.com) wrote: > > On 10 October 2014 12:45, Stephen Frost wrote: > > >> There's a difference between intending that there shouldn't be a way > > >> past security and just making access a matter of walking a longer > > >> route. > > > > > > Throwing random 16-digit numbers and associated information at a credit > > > card processor could be viewed as "walking a longer route" too. The > > > same goes for random key searches or password guesses. > > > > But those would need to be exhaustive, and in nearly all cases, > > impractical. > > That would be exactly the idea with this- we make it impractical to get > at the unredacted information. > For fun I gave the search a try. create table cards (id serial, cc bigint); insert into cards (cc) SELECT CAST(random() * AS bigint) FROM generate_series(1,1); \timing on WITH RECURSIVE t(id, range_min, range_max) AS ( SELECT id, 1::bigint, FROM cards UNION ALL SELECT id , CASE WHEN cc >= range_avg THEN range_avg ELSE range_min END , CASE WHEN cc <= range_avg THEN range_avg ELSE range_max END FROM (SELECT id, (range_min + range_max) / 2 AS range_avg, range_min, range_max FROM t ) AS t_avg JOIN cards USING (id) WHERE range_min != range_max ) SELECT id, range_min AS cc FROM t WHERE range_min = range_max; On my laptop I can pull all 10,000 card numbers in less than 1 second. For a text based item I don't imagine it would be much different. Numbers are pretty easy to work with though.
Re: [HACKERS] A worst case for qsort
Sigh. Found another example. A table with 15 million entries and a unique key on filesystem location for things users created via a web interface. Entries all begin with /usr/home/ ... This one is frequently sorted as batch operations against the files are performed in alphabetical order to reduce conflict issues that a random ordering may cause between jobs. regards, Rod On Thu, Aug 7, 2014 at 5:23 PM, Rod Taylor wrote: > > On Thu, Aug 7, 2014 at 3:06 PM, Peter Geoghegan wrote: > >> I think that pre-sorted, all-unique text datums, that have all >> differences beyond the first 8 bytes, that the user happens to >> actually want to sort are fairly rare. > > > While I'm sure it's not common, I've seen a couple of ten-million tuple > tables having a URL column as primary key where 98% of the entries begin > with 'http://www.' > > So, that exact scenario is out there. >
Re: [HACKERS] A worst case for qsort
On Thu, Aug 7, 2014 at 3:06 PM, Peter Geoghegan wrote: > I think that pre-sorted, all-unique text datums, that have all > differences beyond the first 8 bytes, that the user happens to > actually want to sort are fairly rare. While I'm sure it's not common, I've seen a couple of ten-million tuple tables having a URL column as primary key where 98% of the entries begin with 'http://www.' So, that exact scenario is out there.
Re: [HACKERS] Suppressing unused subquery output columns
On Thu, Jun 5, 2014 at 10:27 PM, Tom Lane wrote: > I'm not entirely convinced that it's worth the extra planning cycles, > though. Given the small number of complaints to date, it might not > be worth doing this. Thoughts? > Would this avoid execution of expensive functions in views when their output is discarded? -- On 9.3 CREATE TABLE data (col1 serial primary key); INSERT INTO data DEFAULT VALUES; INSERT INTO data DEFAULT VALUES; CREATE OR REPLACE VIEW v AS select *, (pg_sleep(1))::text FROM data; t=# explain analyze select col1 from v; QUERY PLAN -- Subquery Scan on v (cost=0.00..76.00 rows=2400 width=4) (actual time=1001.086..2002.217 rows=2 loops=1) -> Seq Scan on data (cost=0.00..52.00 rows=2400 width=4) (actual time=1001.083..2002.210 rows=2 loops=1) Total runtime: 2002.268 ms (3 rows) regards, Rod
Re: [HACKERS] How can we make beta testing better?
On Tue, Apr 15, 2014 at 5:47 PM, Josh Berkus wrote: > Hackers, > > I think 9.3 has given us evidence that our users aren't giving new > versions of PostgreSQL substantial beta testing, or if they are, they > aren't sharing the results with us. > > How can we make beta testing better and more effective? How can we get > more users to actually throw serious workloads at new versions and share > the results? > > I've tried a couple of things over the last two years and they haven't > worked all that well. Since we're about to go into another beta testing > period, we need something new. Ideas? > I think it boils down to making it really easy to create a workload generator. Most companies have simple single-threaded regression tests for functionality but very few companies have good parallel workload generators which reflect activities in their production environment. A documented beta test process/toolset which does the following would help: 1) Enables full query logging 2) Creates a replica of a production DB, record $TIME when it stops. 3) Allow user to make changes (upgrade to 9.4, change hardware, change kernel settings, ...) 4) Plays queries from the CSV logs starting from $TIME mimicking actual timing and transaction boundaries If Pg can make it easy to duplicate activities currently going on in production inside another environment, I would be pleased to fire a couple billion queries through it over the next few weeks. #4 should include reporting useful to the project, such as a sampling of queries which performed significantly worse and a few relative performance stats for overall execution time.
Re: [HACKERS] Changeset Extraction v7.3
On Tue, Jan 28, 2014 at 4:56 PM, Andres Freund wrote: > On 2014-01-28 21:48:09 +, Thom Brown wrote: > > On 28 January 2014 21:37, Robert Haas wrote: > > > On Tue, Jan 28, 2014 at 11:53 AM, Robert Haas > wrote: > > >> I've rebased it here and am hacking on it still. > > > > > > Andres and I are going back and forth between our respective git repos > > > hacking on this, and I think we're getting there, but I have a > > > terminological question which I'd like to submit to a wider audience: > > > > > > The point of Andres's patch set is to introduce a new technology > > > called logical decoding; that is, the ability to get a replication > > > stream that is based on changes to tuples rather than changes to > > > blocks. It could also be called logical replication. In these > > > patches, our existing replication is referred to as "physical" > > > replication, which sounds kind of funny to me. Anyone have another > > > suggestion? > > > > Logical and Binary replication? > > Unfortunately changeset extraction output's can be binary data... > Perhaps Logical and Block? The existing replication mechanism is similar to block-based disk backups. It's the whole thing (not parts) and doesn't have any concept of database/directory.
Re: [HACKERS] GIN improvements part2: fast scan
I tried again this morning using gin-packed-postinglists-16.patch and gin-fast-scan.6.patch. No crashes. It is about a 0.1% random sample of production data (10,000,000 records) with the below structure. Pg was compiled with debug enabled in both cases. Table "public.kp" Column | Type | Modifiers +-+--- id | bigint | not null string | text| not null score1 | integer | score2 | integer | score3 | integer | score4 | integer | Indexes: "kp_pkey" PRIMARY KEY, btree (id) "kp_string_key" UNIQUE CONSTRAINT, btree (string) "textsearch_gin_idx" gin (to_tsvector('simple'::regconfig, string)) WHERE score1 IS NOT NULL This is a query tested. All data is in Pg buffer cache for these timings. Words like "the" and "and" are very common (~9% of entries, each) and a word like "hotel" is much less common (~0.2% of entries). SELECT id,string FROM kp WHERE score1 IS NOT NULL AND to_tsvector('simple', string) @@ to_tsquery('simple', ?) -- ? is substituted with the query strings ORDER BY score1 DESC, score2 ASC LIMIT 1000; Limit (cost=56.04..56.04 rows=1 width=37) (actual time=250.010..250.032 rows=142 loops=1) -> Sort (cost=56.04..56.04 rows=1 width=37) (actual time=250.008..250.017 rows=142 loops=1) Sort Key: score1, score2 Sort Method: quicksort Memory: 36kB -> Bitmap Heap Scan on kp (cost=52.01..56.03 rows=1 width=37) (actual time=249.711..249.945 rows=142 loops=1) Recheck Cond: ((to_tsvector('simple'::regconfig, string) @@ '''hotel'' & ''and'' & ''the'''::tsquery) AND (score1 IS NOT NULL)) -> Bitmap Index Scan on textsearch_gin_idx (cost=0.00..52.01 rows=1 width=0) (actual time=249.681..249.681 rows=142 loops=1) Index Cond: (to_tsvector('simple'::regconfig, string) @@ '''hotel'' & ''and'' & ''the'''::tsquery) Total runtime: 250.096 ms Times are from \timing on. MASTER === the: 888.436 ms 926.609 ms 885.502 ms and: 944.052 ms 937.732 ms 920.050 ms hotel: 53.992 ms57.039 ms65.581 ms and & the & hotel: 260.308 ms 248.275 ms 248.098 ms These numbers roughly match what we get with Pg 9.2. The time savings between 'the' and 'and & the & hotel' is mostly heap lookups for the score and the final sort. The size of the index on disk is about 2% smaller in the patched version. PATCHED === the: 1055.169 ms 1081.976 ms 1083.021 ms and: 912.173 ms 949.364 ms 965.261 ms hotel: 62.591 ms 64.341 ms62.923 ms and & the & hotel: 268.577 ms 259.293 ms 257.408 ms hotel & and & the: 253.574 ms 258.071 ms 250.280 ms I was hoping that the 'and & the & hotel' case would improve with this patch to be closer to the 'hotel' search, as I thought that was the kind of thing it targeted. Unfortunately, it did not. I actually applied the patches, compiled, initdb/load data, and ran it again thinking I made a mistake. Reordering the terms 'hotel & and & the' doesn't change the result. On Fri, Nov 15, 2013 at 1:51 AM, Alexander Korotkov wrote: > On Fri, Nov 15, 2013 at 3:25 AM, Rod Taylor wrote: > >> I checked out master and put together a test case using a small >> percentage of production data for a known problem we have with Pg 9.2 and >> text search scans. >> >> A small percentage in this case means 10 million records randomly >> selected; has a few billion records. >> >> >> Tests ran for master successfully and I recorded timings. >> >> >> >> Applied the patch included here to master along with >> gin-packed-postinglists-14.patch. >> Run make clean; ./configure; make; make install. >> make check (All 141 tests passed.) >> >> initdb, import dump >> >> >> The GIN index fails to build with a segfault. >> > > Thanks for testing. See fixed version in thread about packed posting lists. > > -- > With best regards, > Alexander Korotkov. >
Re: [HACKERS] GIN improvements part2: fast scan
I checked out master and put together a test case using a small percentage of production data for a known problem we have with Pg 9.2 and text search scans. A small percentage in this case means 10 million records randomly selected; has a few billion records. Tests ran for master successfully and I recorded timings. Applied the patch included here to master along with gin-packed-postinglists-14.patch. Run make clean; ./configure; make; make install. make check (All 141 tests passed.) initdb, import dump The GIN index fails to build with a segfault. DETAIL: Failed process was running: CREATE INDEX textsearch_gin_idx ON kp USING gin (to_tsvector('simple'::regconfig, string)) WHERE (score1 IS NOT NULL); #0 XLogCheckBuffer (holdsExclusiveLock=1 '\001', lsn=lsn@entry=0x7fffcf341920, bkpb=bkpb@entry=0x7fffcf341960, rdata=0x468f11 , rdata=0x468f11 ) at xlog.c:2339 #1 0x004b9ddd in XLogInsert (rmid=rmid@entry=13 '\r', info=info@entry=16 '\020', rdata=rdata@entry=0x7fffcf341bf0) at xlog.c:936 #2 0x00468a9e in createPostingTree (index=0x7fa4e8d31030, items=items@entry=0xfb55680, nitems=nitems@entry=762, buildStats=buildStats@entry=0x7fffcf343dd0) at gindatapage.c:1324 #3 0x004630c0 in buildFreshLeafTuple (buildStats=0x7fffcf343dd0, nitem=762, items=0xfb55680, category=, key=34078256, attnum=, ginstate=0x7fffcf341df0) at gininsert.c:281 #4 ginEntryInsert (ginstate=ginstate@entry=0x7fffcf341df0, attnum=, key=34078256, category=, items=0xfb55680, nitem=762, buildStats=buildStats@entry=0x7fffcf343dd0) at gininsert.c:351 #5 0x004635b0 in ginbuild (fcinfo=) at gininsert.c:531 #6 0x00718637 in OidFunctionCall3Coll (functionId=functionId@entry=2738, collation=collation@entry=0, arg1=arg1@entry=140346257507968, arg2=arg2@entry=140346257510448, arg3=arg3@entry=32826432) at fmgr.c:1649 #7 0x004ce1da in index_build (heapRelation=heapRelation@entry=0x7fa4e8d30680, indexRelation=indexRelation@entry=0x7fa4e8d31030, indexInfo=indexInfo@entry=0x1f4e440, isprimary=isprimary@entry=0 '\000', isreindex=isreindex@entry=0 '\000') at index.c:1963 #8 0x004ceeaa in index_create (heapRelation=heapRelation@entry=0x7fa4e8d30680, indexRelationName=indexRelationName@entry=0x1f4e660 "textsearch_gin_knn_idx", indexRelationId=16395, indexRelationId@entry=0, relFileNode=, indexInfo=indexInfo@entry=0x1f4e440, indexColNames=indexColNames@entry=0x1f4f728, accessMethodObjectId=accessMethodObjectId@entry=2742, tableSpaceId=tableSpaceId@entry=0, collationObjectId=collationObjectId@entry=0x1f4fcc8, classObjectId=classObjectId@entry=0x1f4fce0, coloptions=coloptions@entry=0x1f4fcf8, reloptions=reloptions@entry=0, isprimary=0 '\000', isconstraint=0 '\000', deferrable=0 '\000', initdeferred=0 '\000', allow_system_table_mods=0 '\000', skip_build=0 '\000', concurrent=0 '\000', is_internal=0 '\000') at index.c:1082 #9 0x00546a78 in DefineIndex (stmt=, indexRelationId=indexRelationId@entry=0, is_alter_table=is_alter_table@entry=0 '\000', check_rights=check_rights@entry=1 '\001', skip_build=skip_build@entry=0 '\000', quiet=quiet@entry=0 '\000') at indexcmds.c:594 #10 0x0065147e in ProcessUtilitySlow (parsetree=parsetree@entry=0x1f7fb68, queryString=0x1f7eb10 "CREATE INDEX textsearch_gin_idx ON kp USING gin (to_tsvector('simple'::regconfig, string)) WHERE (score1 IS NOT NULL);", context=, params=params@entry=0x0, completionTag=completionTag@entry=0x7fffcf344c10 "", dest=) at utility.c:1163 #11 0x0065079e in standard_ProcessUtility (parsetree=0x1f7fb68, queryString=, context=, params=0x0, dest=, completionTag=0x7fffcf344c10 "") at utility.c:873 #12 0x0064de61 in PortalRunUtility (portal=portal@entry=0x1f4c350, utilityStmt=utilityStmt@entry=0x1f7fb68, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x1f7ff08, completionTag=completionTag@entry=0x7fffcf344c10 "") at pquery.c:1187 #13 0x0064e9e5 in PortalRunMulti (portal=portal@entry=0x1f4c350, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x1f7ff08, altdest=altdest@entry=0x1f7ff08, completionTag=completionTag@entry=0x7fffcf344c10 "") at pquery.c:1318 #14 0x0064f459 in PortalRun (portal=portal@entry=0x1f4c350, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x1f7ff08, altdest=altdest@entry=0x1f7ff08, completionTag=completionTag@entry=0x7fffcf344c10 "") at pquery.c:816 #15 0x0064d2d5 in exec_simple_query ( query_string=0x1f7eb10 "CREATE INDEX textsearch_gin_idx ON kp USING gin (to_tsvector('simple'::regconfig, string)) WHERE (score1 IS NOT NULL);") at postgres.c:1048 #16 PostgresMain (argc=, argv=argv@entry=0x1f2ad40, dbname=0x1f2abf8 "rbt", username=) at postgres.c:3992 #17 0x0045b1b4 in BackendRun (port=0x1f47280) at postmaster.c:4085 #18 BackendStartup (port=0x1f47280) at postmaster.c:3774 #19 ServerLoop () at postmaster.c:1585 #20 0x00
Re: [HACKERS] GIN improvements part2: fast scan
On Fri, Nov 15, 2013 at 2:42 PM, Alexander Korotkov wrote: > On Fri, Nov 15, 2013 at 11:39 PM, Rod Taylor wrote: > >> >> The patched index is 58% of the 9.4 master size. 212 MB instead of 365 MB. >> > > Good. That's meet my expectations :) > You mention that both master and patched versions was compiled with debug. > Was cassert enabled? > Just debug. I try not to do performance tests with assertions on. Patch 7 gives the results I was looking for on this small sampling of data. gin-fast-scan.6.patch/9.4 master performance = the: 1147.413 ms 1159.360 ms 1122.549 ms and: 1035.540 ms 999.514 ms 1003.042 ms hotel: 57.670 ms 61.152 ms58.862 ms and & the & hotel: 266.121 ms 256.711 ms 267.011 ms hotel & and & the: 260.213 ms 254.055 ms 255.611 ms gin-fast-scan.7.patch = the: 1091.735 ms 1068.909 ms 1076.474 ms and: 985.690 ms 972.833 ms 948.286 ms hotel: 60.756 ms 59.028 ms57.836 ms and & the & hotel: 50.391 ms 38.715 ms46.168 ms hotel & and & the: 45.395 ms 40.880 ms43.978 ms Thanks, Rod
Re: [HACKERS] GIN improvements part2: fast scan
On Fri, Nov 15, 2013 at 2:26 PM, Alexander Korotkov wrote: > On Fri, Nov 15, 2013 at 11:18 PM, Rod Taylor wrote: > >> 2%. >> >> It's essentially sentence fragments from 1 to 5 words in length. I wasn't >> expecting it to be much smaller. >> >> 10 recent value selections: >> >> white vinegar reduce color running >> vinegar cure uti >> cane vinegar acidity depends parameter >> how remedy fir clogged shower >> use vinegar sensitive skin >> home remedies removing rust heating >> does non raw apple cider >> home remedies help maintain healthy >> can vinegar mess up your >> apple cide vineger ph balance >> > > I didn't get why it's not significantly smaller. Is it possible to share > dump? > Sorry, I reported that incorrectly. It's not something I was actually looking for and didn't pay much attention to at the time. The patched index is 58% of the 9.4 master size. 212 MB instead of 365 MB.
Re: [HACKERS] GIN improvements part2: fast scan
2%. It's essentially sentence fragments from 1 to 5 words in length. I wasn't expecting it to be much smaller. 10 recent value selections: white vinegar reduce color running vinegar cure uti cane vinegar acidity depends parameter how remedy fir clogged shower use vinegar sensitive skin home remedies removing rust heating does non raw apple cider home remedies help maintain healthy can vinegar mess up your apple cide vineger ph balance regards, Rod On Fri, Nov 15, 2013 at 12:51 PM, Alexander Korotkov wrote: > On Fri, Nov 15, 2013 at 6:57 PM, Rod Taylor wrote: > >> I tried again this morning using gin-packed-postinglists-16.patch and >> gin-fast-scan.6.patch. No crashes. >> >> It is about a 0.1% random sample of production data (10,000,000 records) >> with the below structure. Pg was compiled with debug enabled in both cases. >> >> Table "public.kp" >> Column | Type | Modifiers >> +-+--- >> id | bigint | not null >> string | text| not null >> score1 | integer | >> score2 | integer | >> score3 | integer | >> score4 | integer | >> Indexes: >> "kp_pkey" PRIMARY KEY, btree (id) >> "kp_string_key" UNIQUE CONSTRAINT, btree (string) >> "textsearch_gin_idx" gin (to_tsvector('simple'::regconfig, string)) >> WHERE score1 IS NOT NULL >> >> >> >> This is a query tested. All data is in Pg buffer cache for these timings. >> Words like "the" and "and" are very common (~9% of entries, each) and a >> word like "hotel" is much less common (~0.2% of entries). >> >> SELECT id,string >> FROM kp >>WHERE score1 IS NOT NULL >> AND to_tsvector('simple', string) @@ to_tsquery('simple', ?) >> -- ? is substituted with the query strings >> ORDER BY score1 DESC, score2 ASC >> LIMIT 1000; >> >> Limit (cost=56.04..56.04 rows=1 width=37) (actual time=250.010..250.032 >> rows=142 loops=1) >>-> Sort (cost=56.04..56.04 rows=1 width=37) (actual >> time=250.008..250.017 rows=142 loops=1) >> Sort Key: score1, score2 >> Sort Method: quicksort Memory: 36kB >> -> Bitmap Heap Scan on kp (cost=52.01..56.03 rows=1 width=37) >> (actual time=249.711..249.945 rows=142 loops=1) >>Recheck Cond: ((to_tsvector('simple'::regconfig, string) >> @@ '''hotel'' & ''and'' & ''the'''::tsquery) AND (score1 IS NOT NULL)) >>-> Bitmap Index Scan on textsearch_gin_idx >> (cost=0.00..52.01 rows=1 width=0) (actual time=249.681..249.681 rows=142 >> loops=1) >> Index Cond: (to_tsvector('simple'::regconfig, >> string) @@ '''hotel'' & ''and'' & ''the'''::tsquery) >> Total runtime: 250.096 ms >> >> >> >> Times are from \timing on. >> >> MASTER >> === >> the: 888.436 ms 926.609 ms 885.502 ms >> and: 944.052 ms 937.732 ms 920.050 ms >> hotel: 53.992 ms57.039 ms65.581 ms >> and & the & hotel: 260.308 ms 248.275 ms 248.098 ms >> >> These numbers roughly match what we get with Pg 9.2. The time savings >> between 'the' and 'and & the & hotel' is mostly heap lookups for the score >> and the final sort. >> >> >> >> The size of the index on disk is about 2% smaller in the patched version. >> >> PATCHED >> === >> the: 1055.169 ms 1081.976 ms 1083.021 ms >> and: 912.173 ms 949.364 ms 965.261 ms >> hotel: 62.591 ms 64.341 ms62.923 ms >> and & the & hotel: 268.577 ms 259.293 ms 257.408 ms >> hotel & and & the: 253.574 ms 258.071 ms 250.280 ms >> >> I was hoping that the 'and & the & hotel' case would improve with this >> patch to be closer to the 'hotel' search, as I thought that was the kind of >> thing it targeted. Unfortunately, it did not. I actually applied the >> patches, compiled, initdb/load data, and ran it again thinking I made a >> mistake. >> >> Reordering the terms 'hotel & and & the' doesn't change the result. >> > > Oh, in this path new consistent method isn't implemented for tsvector > opclass, for array only. Will be fixed soon. > BTW, was index 2% smaller or 2 times smaller? If it's 2% smaller than I > need to know more about your dataset :) > > -- > With best regards, > Alexander Korotkov. > >
Re: [HACKERS] GIN improvements part2: fast scan
I tried again this morning using gin-packed-postinglists-16.patch and gin-fast-scan.6.patch. No crashes during index building. Pg was compiled with debug enabled in both cases. The data is a ~0.1% random sample of production data (10,000,000 records for the test) with the below structure. Table "public.kp" Column | Type | Modifiers +-+--- id | bigint | not null string | text| not null score1 | integer | score2 | integer | score3 | integer | score4 | integer | Indexes: "kp_pkey" PRIMARY KEY, btree (id) "kp_string_key" UNIQUE CONSTRAINT, btree (string) "textsearch_gin_idx" gin (to_tsvector('simple'::regconfig, string)) WHERE score1 IS NOT NULL All data is in Pg buffer cache for these timings. Words like "the" and "and" are very common (~9% of entries, each) and a word like "hotel" is much less common (~0.2% of entries). Below is the query tested: SELECT id,string FROM kp WHERE score1 IS NOT NULL AND to_tsvector('simple', string) @@ to_tsquery('simple', ?) -- ? is substituted with the query strings ORDER BY score1 DESC, score2 ASC LIMIT 1000; Limit (cost=56.04..56.04 rows=1 width=37) (actual time=250.010..250.032 rows=142 loops=1) -> Sort (cost=56.04..56.04 rows=1 width=37) (actual time=250.008..250.017 rows=142 loops=1) Sort Key: score1, score2 Sort Method: quicksort Memory: 36kB -> Bitmap Heap Scan on kp (cost=52.01..56.03 rows=1 width=37) (actual time=249.711..249.945 rows=142 loops=1) Recheck Cond: ((to_tsvector('simple'::regconfig, string) @@ '''hotel'' & ''and'' & ''the'''::tsquery) AND (score1 IS NOT NULL)) -> Bitmap Index Scan on textsearch_gin_idx (cost=0.00..52.01 rows=1 width=0) (actual time=249.681..249.681 rows=142 loops=1) Index Cond: (to_tsvector('simple'::regconfig, string) @@ '''hotel'' & ''and'' & ''the'''::tsquery) Total runtime: 250.096 ms Times are from \timing on. MASTER === the: 888.436 ms 926.609 ms 885.502 ms and: 944.052 ms 937.732 ms 920.050 ms hotel: 53.992 ms57.039 ms65.581 ms and & the & hotel: 260.308 ms 248.275 ms 248.098 ms These numbers roughly match what we get with Pg 9.2. The time savings between 'the' and 'and & the & hotel' is mostly heap lookups for the score and the final sort. The size of the index on disk is about 2% smaller in the patched version. PATCHED === the: 1055.169 ms 1081.976 ms 1083.021 ms and: 912.173 ms 949.364 ms 965.261 ms hotel: 62.591 ms 64.341 ms62.923 ms and & the & hotel: 268.577 ms 259.293 ms 257.408 ms hotel & and & the: 253.574 ms 258.071 ms 250.280 ms I was hoping that the 'and & the & hotel' case would improve with this patch to be closer to the 'hotel' search, as I thought that was the kind of thing it targeted. Unfortunately, it did not. I actually applied the patches, compiled, initdb/load data, and ran it again thinking I made a mistake. Reordering the terms 'hotel & and & the' doesn't change the result. On Fri, Nov 15, 2013 at 1:51 AM, Alexander Korotkov wrote: > On Fri, Nov 15, 2013 at 3:25 AM, Rod Taylor wrote: > >> I checked out master and put together a test case using a small >> percentage of production data for a known problem we have with Pg 9.2 and >> text search scans. >> >> A small percentage in this case means 10 million records randomly >> selected; has a few billion records. >> >> >> Tests ran for master successfully and I recorded timings. >> >> >> >> Applied the patch included here to master along with >> gin-packed-postinglists-14.patch. >> Run make clean; ./configure; make; make install. >> make check (All 141 tests passed.) >> >> initdb, import dump >> >> >> The GIN index fails to build with a segfault. >> > > Thanks for testing. See fixed version in thread about packed posting lists. > > -- > With best regards, > Alexander Korotkov. >
Re: [HACKERS] pgbench progress report improvements
On Sat, Sep 21, 2013 at 4:55 AM, Fabien COELHO wrote: > > > I suggest getting the term "stddev" in there somehow, maybe like this: >> >> progress: 37.0 s, 115.2 tps, latency avg=8.678 ms stddev=1.792 >> > > My issue is to try to keep the line width under control so as to avoid > line breaks in the terminal. Under throttling, there is also the time lag > appended to the line. > > Moreover, using 'xxx=figure" breaks simple "cut" pipelining to extract the > figures, so I would prefer to stick to spaces. > > Maybe: > > progress: 36.0 s, 115.2 tps, lat avg 9.678 ms stddev 1.792, lag 0.143 ms > > but I liked my "+-" approach:-) > 100 +- 3 implies a range of 97 to 103 and no values are outside of that range.
Re: [HACKERS] record identical operator
On Tue, Sep 17, 2013 at 8:23 AM, Kevin Grittner wrote: > > Of course, that begs the question of whether == is already "taken". > If not, we could knock one '=' off of everything above except for > "equals". What existing uses are known for == ? > == is already taken as a common typo in plpgsql scripts. I strongly prefer if this remained an error. IF foo == bar THEN ...
[HACKERS] FKey not enforced resulting in broken Dump/Reload
A poorly coded trigger on the referencing table has the ability to break foreign keys, and as a result create a database which cannot be dumped and reloaded. The BEFORE DELETE trigger accidentally does RETURN NEW, which suppresses the DELETE action by the foreign key trigger. This allows the record from the referenced table to be deleted and the record in the referencing table to remain in place. While I don't expect Pg to do what the coder meant, but it should throw an error and not leave foreign key'd data in an invalid state. This applies to both 9.1 and 9.2. Please see attached bug.sql. bug.sql 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
[HACKERS] GIN over array of ENUMs
I wish to create this data structure but GIN does not currently support an array of ENUM. Is intarray() a good place to look into adding ENUM support or is there already an operator class for working supports enums that I simply don't see at the moment. This is being done as an alternative to a very large number of boolean columns which are rarely true (under 1%). CREATE TYPE feature AS ENUM ('item1', 'item2', 'item3'); CREATE TABLE test (id serial PRIMARY KEY, features feature[]); CREATE INDEX test_features_idx ON test USING GIN (features, id); ERROR: data type feature[] has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type. Thanks in advance, Rod
[HACKERS] Time bug with small years
I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 (1 row) sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:17:32-05 (1 row) sk_test=# select '1896-01-01'::timestamp with time zone; timestamptz 1896-01-01 00:00:00-05 (1 row) sk_test=# show timezone; TimeZone --- localtime (1 row) sk_test=# set timezone= 'est5edt'; SET sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:00:00-05 (1 row) sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz 1894-01-01 00:00:00-05 (1 row) I can duplicate with the exact same version of Pg on Intel hardware with kernel: Linux infongd2888 2.6.28.8-20101130b-iscsi-ntacker-fasync-mremap-amd-sec6-grsec #1 SMP Tue Nov 30 18:27:29 CET 2010 i686 GNU/Linux
Re: [HACKERS] foreign keys for array/period contains relationships
On Mon, Oct 25, 2010 at 15:11, Peter Eisentraut wrote: > Example #4: PK is period, FK is timestamp. FK must be contained in some > PK period. > > CREATE TABLE pk (a period PRIMARY KEY, ...); > > CREATE TABLE fk (x timestamp REFERENCES pk (a), ...); > > As above, we can probably arrange the operator knowledge to make these > checks. But I think additionally, you'd need an exclusion constraint on > the PK side to ensure nonoverlapping arrays/periods so that on > update/delete restrict as well as cascading deletes work. > Additional interesting examples involve IP network containment using > inet/cidr or ip4/ip4r. There, you'd probably need additional syntax to > tell the system explicitly which operators to use. > There are a large number of use-cases for this type of foreign key with geometry ( PostGIS ) types as well. Point references Area or Line, Area references Area, etc.
Re: [HACKERS] WIP: cross column correlation ...
> > But it's not the same as tracking *sections of a table*. > > I dunno. I imagine if you have a "section" of a table in different > storage than other sections, you created a tablespace and moved the > partition holding that section there. Otherwise, how do you prevent the > tuples from moving to other "sections"? (We don't really have a concept > of "sections" of a table.) > > Section could be as simple as being on the inner or outer part of a single disk, or as complicated as being on the SSD cache of a spinning disk, or in the multi-gigabyte cache on the raid card or SAN due to being consistently accessed. Section is the wrong word. If primary key values under 10 million are consistently accessed, they will be cached even if they do get moved through the structure. Values over 10M may be fast if on the same page as the other value but probably aren't. This is very evident when dealing with time based data in what can be a very large structure. 1% may be very hot and in memory while 99% is not. Partitioning only helps if you can predict what will be hot in the future. Sometimes an outside source (world events) impacts what section of the structure is hot. regards, Rod
Re: [HACKERS] WIP: cross column correlation ...
On Fri, Feb 25, 2011 at 14:26, Alvaro Herrera wrote: > Excerpts from Rod Taylor's message of vie feb 25 14:03:58 -0300 2011: > > > How practical would it be for analyze to keep a record of response times > for > > given sections of a table as it randomly accesses them and generate some > > kind of a map for expected response times for the pieces of data it is > > analysing? > > I think what you want is random_page_cost that can be tailored per > tablespace. > > Yes, that can certainly help but does nothing to help with finding typical hot-spots or cached sections of the table and sending that information to the planner. Between Analyze random sampling and perhaps some metric during actual IO of random of queries we should be able to determine and record which pieces of data tend to be hot/in cache, or readily available and what data tends not to be. If the planner knew that the value "1" tends to have a much lower cost to fetch than any other value in the table (it is cached or otherwise readily available), it can choose a plan better suited toward that.
Re: [HACKERS] WIP: cross column correlation ...
> 4. Even if we could accurately estimate the percentage of the table > that is cached, what then? For example, suppose that a user issues a > query which retrieves 1% of a table, and we know that 1% of that table > is cached. How much of the data that the user asked for is cache? > Hard to say, right? It could be none of it or all of it. The second > scenario is easy to imagine - just suppose the query's been executed > twice. The first scenario isn't hard to imagine either. > > I have a set of slow disks which can impact performance nearly as much as in cached in memory versus the fast disks. How practical would it be for analyze to keep a record of response times for given sections of a table as it randomly accesses them and generate some kind of a map for expected response times for the pieces of data it is analysing? It may well discover, on it's own, that recent data (1 month old or less) has a random read response time of N, older data (1 year old) in a different section of the relation tends to have a response time of 1000N, and really old data (5 year old) tends to have a response time of 3000N.
Re: [HACKERS] 8.3 to 8.4 Upgrade issues
On Tue, Aug 10, 2010 at 13:49, Tom Lane wrote: > Rod Taylor writes: >> Does anybody have experience on the cost, if any, of making this change? > >> Pg 8.3: >> Encoding: SQL_ASCII >> LC_COLLATE: en_US >> LC_CTYPE: en_US > >> Pg 8.4: >> Encoding: SQL_ASCII >> Collation: en_US.UTF-8 >> Ctype: en_US.UTF-8 > > Well, *both* of those settings collections are fundamentally > wrong/bogus; any collation/ctype setting other than "C" is unsafe if > you've got encoding set to SQL_ASCII. But without knowing what your > platform thinks "en_US" means, it's difficult to speculate about what > the difference between them is. I suppose that your libc's default > assumption about encoding is not UTF-8, else these would be equivalent. > If it had been assuming a single-byte encoding, then telling it UTF8 > instead could lead to a significant slowdown in strcoll() speed ... > but I would think that would mainly be a problem if you had a lot of > non-ASCII data, and if you did, you'd be having a lot of problems other > than just performance. Have you noticed any change in sorting behavior? Agreed with it being an interesting choice of settings. Nearly all of the data is 7-bit ASCII and what isn't seems to be a mix of UTF8, LATIN1, and LATIN15. I'm pretty sure it interpreted en_US to be LATIN1. There haven't been any noticeable changes in sorting order that I know of. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.3 to 8.4 Upgrade issues
We recently upgraded from 8.3 to 8.4 and have seen a performance degredation which we are trying to explain and I have been asked to get a second opinion on the cost of going from LATIN1 to UTF8 (Collation and CType) where the encoding remained SQL_ASCII.. Does anybody have experience on the cost, if any, of making this change? Pg 8.3: Encoding: SQL_ASCII LC_COLLATE: en_US LC_CTYPE: en_US Pg 8.4: Encoding: SQL_ASCII Collation: en_US.UTF-8 Ctype: en_US.UTF-8 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CIText and pattern_ops
Is there any particular reason why the citext module doesn't have citext_pattern_ops operator family? Specifically, I wish to index for this type of query: ... WHERE citext_column LIKE 'Foo%'; This, of course, is equivalent to ILIKE 'Foo%' which does not appear to be indexable without using a functional index ( lower(citext_column) ). -- 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] PG 9.0 and standard_conforming_strings
On Wed, Feb 3, 2010 at 13:20, Robert Haas wrote: > On Wed, Feb 3, 2010 at 12:34 PM, Greg Sabino Mullane > wrote: >> Perl (DBD::Pg anyway) has been compatible since May 2008. > > I would interpret that to mean that there is a significant possibility > that a too-old DBD::Pg could get used with a new PostgreSQL, and > therefore we shouldn't change anything for 9.0. May 2008 is not that > long ago, especially for people running systems like RHEL with > five-year major release cycles. I fall into this camp with a few machines still running standard RHEL 4 which I believe has DBD::Pg 1.32 installed. We do keep up to date with PostgreSQL but the machines connecting to it include everything from brand new web servers through to ancient machines in accounting running reports. As much as I would like GUCs to disappear I think this one should proceed cautiously and probably be a 9.1 or even 9.2 item. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] update_process_title=off and logger, wal, ... processes
With the update_process_title parameter set to off some PostgreSQL processes still change their ps title to a different name than the default. I appreciate this setting came about for performance reasons which the logger, wal writer, autovacuum, and stats collector would not have but I actually require the default and patched init_ps_display() to skip changing the name. It just surprised me that the titles still changed a single time. I (stupidly?) installed PostgreSQL into a hostile environment which didn't like this and decided to kill the processes as a result. Unfortunately, I cannot change the environment. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] NOT IN Doesn't use Anti Joins?
I'm sure there is a good reason why NOT IN will not use an Anti-Join plan equivalent to NOT EXISTS due to NULL handling, but in this particular case the value being compared is in the PRIMARY KEY of both structures being joined. The NOT IN plan was killed after 10 minutes. The NOT EXISTS plan returned data in roughly 10ms. Is there a reason why the NOT IN plan could not use Anti-Joins when the column being compared against is guaranteed to be NOT NULL? Too much planner overhead to determine nullness of the column? sk=# explain select * from source_reb_listing where listing_id not in (select listing_id from source_reb_listing_specs) order by file_id desc limit 5; QUERY PLAN - Limit (cost=729015.39..3420463.83 rows=5 width=28) -> Index Scan Backward using source_reb_listing_fileid_idx on source_reb_listing (cost=729015.39..169537219655.96 rows=314954 width=28) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=729015.39..1185280.74 rows=32810035 width=8) -> Seq Scan on source_reb_listing_specs (cost=0.00..568040.35 rows=32810035 width=8) (6 rows) sk=# explain select * from source_reb_listing where not exists (select * from source_reb_listing_specs as t where t.listing_id = source_reb_listing.listing_id) order by file_id desc limit 5; QUERY PLAN - Limit (cost=0.00..35.31 rows=5 width=28) -> Nested Loop Anti Join (cost=0.00..3880495.87 rows=549496 width=28) -> Index Scan Backward using source_reb_listing_fileid_idx on source_reb_listing (cost=0.00..1107142.20 rows=629907 width=28) -> Index Scan using source_reb_listing_specs_pkey on source_reb_listing_specs t (cost=0.00..1592.74 rows=408 width=8) Index Cond: (t.listing_id = source_reb_listing.listing_id) (5 rows) -- 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] Using views for row-level access control is leaky
> > \c - secretary > > CREATE OR REPLACE FUNCTION expose_person (person text, phone text) > RETURNS bool AS $$ > begin > RAISE NOTICE 'person: % number: %', person, phone; > RETURN true; > END; $$ LANGUAGE plpgsql COST 0.01; > > postgres=> SELECT * FROM phone_number WHERE expose_person(person, phone); > NOTICE: person: public person number: 12345 > NOTICE: person: secret person number: 67890 > person | phone > ---+--- > public person | 12345 > (1 row) > Given RAISE is easily replaced with INSERT into a logging table or another recording mechanism, it needs to be something to push back execution of user based parameters OR something to push forward security clauses. Is there any way of exposing the information using standard SQL or is a procedure required? If a procedure is required, then we simply need a way of ensuring the SECURITY clauses or functions run before all of the things which an expose information (procedures at the moment). How about some kind of a marker on which allows security based constraints to be pushed forward rather than the entire view? CREATE VIEW phone_number AS SELECT person, phone FROM phone_data WHERE SECURITY(phone NOT LIKE '6%'); This still allows complex views and queries to be mostly optimized with a few filters that run very early and in the order they are defined in. Perhaps we go one step further and encourage security filters to be applied to the table directly where possible: CREATE VIEW phone_number AS SELECT person, phone FROM phone_data USING SECURITY FILTER(phone NOT LIKE '6%'); This still allow many optimizations to be applied in complex cases. The planner CREATE VIEW phone_number AS SELECT person, phone, company FROM phone_data USING SECURITY FILTER(phone NOT LIKE '6%') JOIN person USING (person_id) JOIN company USING (company_id) AND person.active AND company.active; \c - secretary SELECT * FROM phone_number WHERE company = 'Frankies Co.'; This still allows a query against phone_number to use the company data first, find the single person (Frankie) within that company, then get his phone number out. The scan against phone_data would be an index scan for person_id BUT applies the SECURITY FILTER as the node immediately around the index scan as a Recheck Condition, similar to how bitmap scans ensure they got the correct and only the correct information. person.active and company.active, and the joins can still be optimized in standard ways. More complex SECURITY FILTER clauses might be applied in the where clause. I.e. CREATE VIEW phone_number AS SELECT person, phone, company FROM phone_data USING SECURITY CLAUSE (phone NOT LIKE '6%') JOIN person USING (person_id) JOIN company USING (company_id) WHERE SECURITY CLAUSE (person.status = company.status) AND person.active AND company.active; This would result in the security check (person.status = company.status) occurring as a filter tied to the join node for person and company which cannot be moved around. Layering is tricky, using the above view: \c - secretary CREATE VIEW company_number AS SELECT * FROM phone_number SECURITY CLAUSE (expose_person(person, phone)); SELECT * FROM company_number; The security clauses are bound to run in the order they are found in the node closes to the data they use. phone_data is immediately run through a Recheck Cond. person/company join node is checked immediately after. Finally, the expose_person() function is run against the now clean data. Oh, This all has the nice side effect of knowing what to hide in explain analyze as well since the specific clauses are marked up. If the user running the query is super user or owner of the view, they see the security clause filters. If they are not, then they get a line like this: SELECT * FROM phone_number WHERE phone = '555-555-'; Bitmap Heap Scan on phone_data (cost=14.25..61.47 rows=258 width=185) Security Cond: ** Hidden due to permissions ** -> Bitmap Index Scan on phone_data_index (cost=0.00..14.19 rows=258 width=0) Index Cond: (phone = '555-555-') -- 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] Could regexp_matches be immutable?
> So, having dismissed my original off-the-cuff answer to Rod, the next > question is what's really going wrong for him. I get this from > a quick trial: I wish I had kept specific notes on what I was actually trying to do. I tried to_number first then the expression as seen below. I guess I saw the error again and assumed it was the same as for to_number. sk=# BEGIN; BEGIN sk=# sk=# create table t1 (col1 text); CREATE TABLE sk=# INSERT INTO t1 values ('Z342432'); INSERT 0 1 sk=# INSERT INTO t1 values ('REW9432'); INSERT 0 1 sk=# sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1; regexp_matches 342432 9432 (2 rows) sk=# sk=# create index t1_idx ON t1 (( (regexp_matches(col1, '(\d+)$'))[1] )); ERROR: index expression cannot return a set sk=# sk=# ROLLBACK; ROLLBACK It is interesting that "citext" seems to be functional with exactly the same statements. sk=# BEGIN; BEGIN sk=# sk=# create table t1 (col1 citext); CREATE TABLE sk=# INSERT INTO t1 values ('Z342432'); INSERT 0 1 sk=# INSERT INTO t1 values ('REW9432'); INSERT 0 1 sk=# sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1; regexp_matches 342432 9432 (2 rows) sk=# sk=# create index t1_idx ON t1 (( (regexp_matches(col1, '(\d+)$'))[1] )); CREATE INDEX sk=# sk=# ROLLBACK; ROLLBACK The function regexp_replace(col1, '^[^0-9]+', '') does seem to do the trick for text. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Could regexp_matches be immutable?
I tried making a functional index based on an expression containing the 2 argument regexp_matches() function. Is there a reason why this function is not marked immutable instead of normal? regards, Rod Taylor -- 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] COPY enhancements
> Yeah. I think it's going to be hard to make this work without having > standalone transactions. One idea would be to start a subtransaction, > insert tuples until one fails, then rollback the subtransaction and > start a new one, and continue on until the error limit is reached. > I've found performance is reasonable, for data with low numbers of errors (say 1 per 100,000 records or less) doing the following: SAVEPOINT bulk; Insert 1000 records using COPY. If there is an error, rollback to bulk, and step through each line individually within its own "individual" subtransaction. All good lines are kept and bad lines are logged; client side control makes logging trivial. The next set of 1000 records is done in bulk again. 1000 records per savepoint seems to be a good point for my data without too much time lost to overhead or too many records to retry due to a failing record. Of course, it is controlled by the client side rather than server side so reporting back broken records is trivial. It may be possible to boost performance by: 1) Having copy remember which specific line caused the error. So it can replace lines 1 through 487 in a subtransaction since it knows those are successful. Run 488 in its on subtransaction. Run 489 through ... in a new subtransaction. 2) Increasing the number of records per subtransaction if data is clean. It wouldn't take long until you were inserting millions of records per subtransaction for a large data set. This should make the subtransaction overhead minimal. Small imports would still run slower but very large imports of clean data should be essentially the same speed in the end.
Re: [HACKERS] remove flatfiles.c
On Tue, Sep 1, 2009 at 19:34, Greg Stark wrote: > On Wed, Sep 2, 2009 at 12:01 AM, Alvaro > Herrera wrote: > >> The use cases where VACUUM FULL wins currently are where storing two > >> copies of the table and its indexes concurrently just isn't practical. > > > > Yeah, but then do you really need to use VACUUM FULL? If that's really > > a problem then there ain't that many dead tuples around. > > That's what I want to believe. But picture if you have, say a > 1-terabyte table which is 50% dead tuples and you don't have a spare > 1-terabytes to rewrite the whole table. > It would be interesting if there was something between VACUUM FULL and CLUSTER which could, say, work on a single 1GB segment at a time in a manner similar to cluster. You would still end up with index bloat like vacuum full, though perhaps not as bad, but shuffling around the tuples should be faster. The idea here is that the files can be truncated individually. Two 500MB files is pretty much the same as a single 1GB file on disk. Of course, I'm hand waving and don't have the technical expertise to figure out if it can be done easily within PostgreSQL.
Re: [HACKERS] Out parameters handling
On Sat, Mar 7, 2009 at 11:32 AM, Tom Lane wrote: > Robert Haas writes: >> On Sat, Mar 7, 2009 at 9:08 AM, Rod Taylor wrote: >>> It wouldn't be so bad if you could assign internal and external column >>> names. > >> This is a good point. Uglifying the parameter names is sort of OK for >> input parameters, but is much more annoying for output parameters. > > How much of this pain would go away if we changed over to the arguably > correct (as in Or*cle does it that way) scoping for names, wherein the > parser first tries to match a name against column names of tables of the > current SQL statement, and only failing that looks to see if they are > plpgsql variables? This would solve all of my conflicts correctly. I nearly always use RETURN QUERY with OUT parameters. An alternative would be the requirement to prefix out parameters with "out", "export", or something similar, so the plain non-prefixed name is never replaced. "b" in the below is the table. I hit this quite a bit since my historical table name might be "foo_bar_baz" which is the same as the most relevant name for the out parameter. I've debated renaming all of my tables t_* on more than one occasion as a workaround in applications which exclusively use functions to access/write data. create or replace function read_some_data_from_data_region(a integer, out b integer) as $$ begin SELECT col INTO out.b FROM b; return; end; $$ language plpgsql; -- 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] Out parameters handling
> actually - function name should be used as label now. This code is working: Not helpful for me. The most typical conflict I have is actually the OUT parameter and table name, not a column of the table. Really don't want to prefix all tables with a hardcoded schema or do variable substitution for loading the document. Not fond of prefixing with function name either as a) many of my functions have very long names and b) they change names occasionally, particularly during development. A short prefix like "out" would be useful. I would immediately start prefixing all uses. rbt=# begin; BEGIN rbt=# create table b (col integer); CREATE TABLE rbt=# insert into b values (2); INSERT 0 1 rbt=# create or replace function fx2(a integer, out b integer) as $$ rbt$# begin rbt$# SELECT col rbt$# INTO fx2.b rbt$# FROM b; rbt$# rbt$# return; rbt$# end; $$ language plpgsql; ERROR: syntax error at or near "$1" LINE 1: SELECT col FROM $1 ^ QUERY: SELECT col FROM $1 CONTEXT: SQL statement in PL/PgSQL function "fx2" near line 4 rbt=# -- 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] Out parameters handling
It wouldn't be so bad if you could assign internal and external column names. Within the function you call the column "v_foo" but the caller of the function receives column "foo" instead. OUT v_foo varchar AS "foo" Another alternative is requiring a prefix like plout for the replacement to occur: ( OUT foo varchar ) BEGIN SELECT foo.somename INTO plout.foo FROM foo WHERE id = 10; RETURN NEXT; RETURN; END; On Sat, Mar 7, 2009 at 8:50 AM, Robert Haas wrote: > On Fri, Mar 6, 2009 at 8:44 PM, Josh Berkus wrote: >> Robert, >> >> Thing is, anybody can institute their own naming convention. I've long used >> v_ as a prefix. Allowing : would save me some keystrokes, but that's about >> it. >> >> --Josh > > True... but there doesn't seem to be any shortage of people who are > annoyed by the current behavior. Maybe we should all just learn to > live with it. > > ...Robert > > -- > 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
Re: FWD: Re: [HACKERS] Updated backslash consistency patch
I would settle for just following the search path as set by the user. If you explicitly include pg_catalog in the search path, then you should see those settings. If you do not explicitly include pg_catalog on the search_path, then it should not find those items. Right now pg_catalog sneaks its way onto the search_path for everybody. That is fine for execution but information listing like this should probably ignore those additions. On Thu, Jan 15, 2009 at 11:50 AM, Tom Lane wrote: > Bruce Momjian writes: > > Tom Lane wrote: > >> I think this falls in the category of "be careful what you wish for, > >> you might get it". It is now blindingly obvious that the folks asking > >> for that had not actually lived with the behavior for any period of > >> time. > > > I got several emails thanking me for applying the patch, so there is > > clearly user-demand for 'S'. > > Were any of them from people who had actually *used* the patch for more > than five minutes? I think this is clearly a case of allowing abstract > consistency considerations to override usability. > > The real problem here is that the 'S' suffix for \dt is a bad precedent > for everything else. If you want consistency then we need to change > that end of things. I think that the idea of a switch to omit system > objects, rather than include them, might work. > >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] WIP: default values for function parameters
How about IS or INTO? param_name IS 3 param_name IS 'some string value' 3 INTO param_name 'some string value' INTO param_name On Fri, Dec 12, 2008 at 8:47 AM, Pavel Stehule wrote: > 2008/12/12 David E. Wheeler : >> On Dec 12, 2008, at 2:39 PM, Tom Lane wrote: >> >>> So I think that really this is never going to fly unless it uses a >>> keyword-looking reserved word. And we're not going to take some short >>> word that's not reserved now and suddenly make it so. So, despite >>> Pavel's objection that the AS syntax proposal might be confused with >>> other uses of AS, I seriously doubt that any proposal is going to get >>> accepted that doesn't recycle AS or some other existing reserved word. > > when I should exactly identify param name, the we should to use any symbols. > >> >> I'm okay with AS if that's the way it has to be, but what about a colon >> right at the end of the label? A cast is two colons, so no conflict there: >> >> SELECT foo(1, name: 'bar', quantity: 10); > > it's look well, but I still prefer some combination with = > > name: = '' > name: => ''' > :name = '' > $name => .. > $name = .. > > Maybe I am too conservative > Pavel > >> >> No doubt I'm missing something… >> >> Best >> >> David > > -- > 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
Re: [HACKERS] New feature request: FlashBack Query
Wrong. When Oracle says it's committed, it's committed. No difference between when, where, and how. In Oracle, the committed version is *always* the first presented to the user... it takes time to go back and look at older versions; but why shouldn't that be a bit slower, it isn't common practice anyway. Same with rollbacks... why should they optimize for them when 97% of transactions commit? Do 97% of transactions commit because Oracle has slow rollbacks and developers are working around that performance issue, or because they really commit? I have watched several developers that would prefer to issue numerous selects to verify things like foreign keys in the application in order to avoid a rollback. Anyway, I don't have experience with big Oracle applications but I'm not so sure that 97% of transactions would commit if rollbacks were cheaper. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
On Thu, 2006-08-17 at 18:32 +0200, Peter Eisentraut wrote: > Is it time to turn on autovacuum by default in 8.2? I know we wanted to > be on the side of caution with 8.1, but perhaps we should evaluate the > experiences now. Comments? I would say yes. I use it on 2 databases over the 200GB mark without any difficulties. One is OLTP and the other acts more like a data warehouse. The defaults could be a little more aggressive for both vacuum and analyze scale_factor settings; 10% and 5% respectively. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] "Constraint exclusion" is not general enough
On Mon, 2006-08-07 at 22:01 -0400, Tom Lane wrote: > "Florian G. Pflug" <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> But you don't have any cost numbers until after you've done the plan. > > > Couldn't this work similar to geqo_effort? The planner could > > try planning the query using only cheap algorithmns, and if > > the cost exceeds a certain value, it'd restart, and use > > more sophisticated methods. > > AFAICS this would be a net loss on average. Most of the time, the > constraint exclusion code doesn't win, and so throwing away all your > planning work to try it is going to be a loser most of the time. If constraint exclusion does not make any changes, mark the plan as invalid, then there is no need to replan. 1. Generate plan cheaply 2. If under $threshold, execute query. The cost of further planning is significant compared to executing this potentially non-optimal plan. 3. Run constraint exclusion. If it changes the clauses due to constraint exclusion, mark the plan as invalid. I assume constraint exclusion is relatively self contained. 4. Invalid plan is replanned. Still valid plan (no potential improvements can be made) is executed. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] "Constraint exclusion" is not general enough
On Mon, 2006-08-07 at 13:44 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > A simple way of doing this might be to use a minimum cost number? > > But you don't have any cost numbers until after you've done the plan. Isn't it possible to find the cost using the straight forward (fast) method, find out what order of magnitude it is in, then do a second pass with additional planner bells and whistles turned on if the first plan had a high estimate? -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] "Constraint exclusion" is not general enough
On Mon, 2006-08-07 at 16:54 +0100, Simon Riggs wrote: > On Fri, 2006-08-04 at 14:40 -0400, Tom Lane wrote: > > I was just looking at Martin Lesser's gripe here: > > http://archives.postgresql.org/pgsql-performance/2006-08/msg00053.php > > about how the planner is not real bright about the filter conditions > > it generates for a simple partitioning layout. In particular it's > > generating scans involving self-contradictory conditions: > > > > Result (cost=0.00..33.20 rows=6 width=36) > >-> Append (cost=0.00..33.20 rows=6 width=36) > > -> Seq Scan on t_parted (cost=0.00..33.20 rows=6 width=36) > >Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 900) AND > > (id1 < 1000)) > > > > which it seems we ought to be bright enough to notice. In particular > > I would argue that turning on constraint_exclusion ought to instruct > > the planner to catch this sort of thing, whereas when it's off we > > ought not expend the cycles. I have a preliminary patch (below) > > that seems to fix it. > > > > The problem I'm having is that this isn't "constraint exclusion" anymore > > --- it will in fact make useful deductions without a table constraint > > anywhere in sight. Should we rename the GUC variable, and if so to what? > > Or just live with the misnomer? I guess plan C would be to invent a > > separate GUC variable for the other kind of test, but I can't see that > > it's worth having two. Thoughts? > > In general, I'd prefer a control that allowed "amount of planning" to be > specified, much in the same way we rate error messages. We really want > just one simple knob that can be turned up or down, no matter how many > new optimizations we add. > > planning_effort = LOW | MEDIUM | HIGH | VERYHIGH | EXHAUSTIVE A simple way of doing this might be to use a minimum cost number? # Minimum cost of query is over 100 before applying mutual_exclusion = 100 Once applied if the filter accomplished something the query is replanned or adjusted to take that change into account. If there were a large number of constraints on t_parted it may well have taken longer to plan than to execute on the 6 rows. If there were 1M rows in the structure, the extra effort would have been well worth it. Ideally we could set the planning time as a percentage of total execution time and let PostgreSQL figure out what should be tried and when, but that means giving a cost to planner functionality and having PostgreSQL plan how to plan. planning_effort = 5% -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: GENERATED ... AS IDENTITY, Was: Re: [HACKERS] Feature Freeze
> > For db restoration (pg_dump), how do you restore to the same values as > > previously if it is always regenerated? By making ALWAYS a suggestion > > for some users instead of always enforced and providing an override > > mechanism for it. I assume it only works for relation owners but I've > > not figured out how the spec does permissions. > > > > ::= > > OVERRIDING USER VALUE > > | OVERRIDING SYSTEM VALUE > > > > In short, pg_dump should append OVERRIDING SYSTEM VALUE to any insert or > > copy for relations with an GENERATED ALWAYS identity column and the > > backend will need to respect that. > > > > Aren't INSERT and COPY distinguished in code paths? Yes, they are separate but they also use the same permission set. Any user can copy into a structure at any time and virtually every restriction will be applied normally (CHECK, DEFAULT, etc.). Copy bypasses Rules, significant parsing overhead since there is no need to look for subselects, and possibly some triggers are bypassed. I'm fairly sure that foreign key triggers fire. In short, COPY doesn't bypass enforcement. GENERATED ALWAYS is part of that enforcement and should be included in that. If it is not included, we cannot recommend GENERATED ALWAYS for uses like recording CURRENT_USER in an audit log since the data could be fudged. > > ALWAYS is really only enforced for anyone who doesn't have permission to > > specify otherwise. > > > > > > Another one that got me is what do you do if you do this: > > > > CREATE TABLE tab (col integer); > > INSERT INTO tab VALUES (10); > > ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY; > > > > What is the value for "tab"."col"? It would seem that the table should > > be rewritten with all values for "col" recalculated -- thus it would be > > '1'. But wait! Can we add the here too to keep the old > > values and change the enforcement for new tuples only? > > > > I don't think we should rewrite existing rows because > when it was inserted, the stored value was valid > according to the rules at that time. What if you > have more than one rows in that table? SERIAL has, until recently, been described as a macro. A tool for setting things up quickly but many parts of which can be changed by hand after-ward. It's not exactly a good source for information on how this structure should work. For one, you can easily override the suggested default a serial gives at any time as any user. The intention of ALWAYS is to prevent exactly that behaviour. I don't have an opinion on ALTER TABLE changes for this one way or the other. It was my intention to advise that a group decision is required and some research into what other databases do in this case. I believe MSSQL and DB2 both implement this functionality. Oh, and one more item. These expressions have the same abilities as a CHECK constraint for referencing other columns. This example comes from an IBM Guide: CREATE TABLE T1(c1 INT, c2 DOUBLE, c3 DOUBLE GENERATED ALWAYS AS (c1 + c2), c4 SMALLINT GENERATED ALWAYS AS (CASE WHEN c1 > c2 THEN 1 ELSE NULL END) ); Here is what IBM has to say about ALTER TABLE and GENERATED ALWAYS: GENERATED Specifies that DB2 generates values for the column. ALWAYS Specifies that DB2 will always generate a value for the column when a row is inserted into the table, or whenever the result value of the generation-expression might change. The result of the expression is stored in the table. GENERATED ALWAYS is the recommended option unless data propagation or unload and reload operations are being performed. GENERATED ALWAYS is the required option for generated columns. BY DEFAULT Specifies that DB2 will generate a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. BY DEFAULT is the recommended option when using data propagation or performing unload and reload operations. identity-options This
Re: GENERATED ... AS IDENTITY, Was: Re: [HACKERS] Feature Freeze
On Tue, 2006-08-01 at 18:10 +0200, Zoltan Boszormenyi wrote: > Hi, > > I have progressed a bit with my pet project, a.k.a $SUBJECT. > > Now GENERATED ALWAYS AS IDENTITY and > GENERATED ALWAYS AS ( expr ) work as > intended. Documentation was also extended. I'm only commenting because I debated trying to implement this feature a couple of times. The ugliness required for pg_dump put me off of doing it. I did not see a test for enforcement during COPY. UPDATE restrictions appear to have been missed as well: 4) If SC specifies an that references a column of which some underlying column is either a generated column or an identity column whose descriptor indicates that values are always generated, then the specified in SC shall consist of a . is the , or the left hand side of the equation. In short, if a column marked GENERATED ALWAYS is updated then it must be to DEFAULT or not provided as an update target. CREATE TABLE tab (col integer GENERATED ALWAYS AS IDENTITY); UPDATE tab SET col = DEFAULT; -- ACCEPTED UPDATE tab SET col = 1; -- ERROR For db restoration (pg_dump), how do you restore to the same values as previously if it is always regenerated? By making ALWAYS a suggestion for some users instead of always enforced and providing an override mechanism for it. I assume it only works for relation owners but I've not figured out how the spec does permissions. ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE In short, pg_dump should append OVERRIDING SYSTEM VALUE to any insert or copy for relations with an GENERATED ALWAYS identity column and the backend will need to respect that. ALWAYS is really only enforced for anyone who doesn't have permission to specify otherwise. Another one that got me is what do you do if you do this: CREATE TABLE tab (col integer); INSERT INTO tab VALUES (10); ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY; What is the value for "tab"."col"? It would seem that the table should be rewritten with all values for "col" recalculated -- thus it would be '1'. But wait! Can we add the here too to keep the old values and change the enforcement for new tuples only? > Some test cases are also included, that shows > that ALTER TABLE ALTER TYPE keeps both > the sequence and the GENERATED ALWAYS > property. Gzipped patch is attached. > > Next steps are: > - pg_dump support > - more ALTER TABLE support for adding and > dropping IDENTITY and GENERATED ALWAYS > features > - more testing > > I still maintain that I don't see any standard > requirement between the GENERATED AS IDENTITY > and NEXT VALUE FOR but obviously both > require SEQUENCE as supported feature > in parallel. I can be proven wrong, though, > but please, quote section# and text where > it can be found in the standard. > > As for why GENERATED ALWAYS AS IDENTITY > is useful? Consider someone who is coming from > another DBMS (Informix, Access, etc.) where > "INSERT INTO table (id, ...) VALUES (0, ...);" > inserts the next value for the autoincrementer field > instead of 0. Leaving out fields from INSERT is > not allowed in the source because of documentation > reasons and writing DEFAULT is not handy or not > found in that legacy DBMS' features. > Multiply it with N applications that was written > that way over the years of the lifespan of a large > project, count in the human resistance to learn > something new (say 2.5x multiplier, but that may be > under-estimated :-) ) and a feature that help porting > easier will be a cheered feature. IIRC Bruce Momjian > himself wrote in this list that ease-of-use features > can boost PostgreSQL userbase pretty quickly. > > So, please, review my patch in it's current state > and decide whether it's a 8.2-worthy feature. > > BTW, is there anyone working on COPY FROM ( select ) feature? > > Thanks in advance and best regards, > Zoltán Böszörményi > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] extension for sql update
On Mon, 2006-07-31 at 17:26 +0200, Peter Eisentraut wrote: > Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane: > > The reason people want this syntax is that they expect to be > > able to write, say, > > > > UPDATE mytab SET (foo, bar, baz) = > > (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); > > I don't find any derivation in the standard that would permit this. The only > thing I could find are variations on > > SET (a) = x -- no parentheses > SET (a, b) = (x, y) > SET (a, b) = ROW (x, y) > > where x and y are some sort of value expression. I would have expected the > sort of thing that you describe, but if you know how to derive that, I'd like > to see it. I believe can be one or more which includes a . gives us the option. For that matter the below portion of gives us: | This breaks down into one or more comma separated s. UPDATE tab SET (...) = ((SELECT foo, bar from a), (select bif,baz from b)); -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 09:52 -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Martijn van Oosterhout wrote: > >> Maybe someone should look into enabling slony to not run as a > >> superuser? > > > That was my initial reaction to this suggestion. But then I realised > > that it might well make sense to have a separate connection-limited > > superuser for Slony purposes (or any other special purpose) alongside an > > unlimited superuser. > > Actually, the real question in my mind is why Slony can't be trusted > to use the right number of connections to start with. If you don't > trust it that far, what are you doing letting it into your database as > superuser to start with? I generally try to apply reasonable restrictions on all activities that take place on my systems unless the machine was dedicated for that task (in which case the limitations are those of the machine). When things go wrong, and they almost always do eventually, these types of restrictions ensure that only the one process grinds to a halt instead of the entire environment. Cron jobs are another area that are frequently implemented incorrectly. Implementing checks to see if it is already running is overlooked enough that I would like to restrict them as well. This is less important since roles now allow multiple users to take ownership of a relation; less jobs that need to run as a superuser. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 15:00 +0200, Martijn van Oosterhout wrote: > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > > It appears that the superuser does not have connection limit > > enforcement. I think this should be changed. > > So if some admin process goes awry and uses up all the connection > slots, how does the admin get in to see what's happening? If there's a > limit you're not really superuser, are you? Work this one through. If an admin process goes awry and uses up all the connection slots it has reached max_connections AND used superuser_reserved_connections as well. This means an admin cannot get in to see what is happening. That's what happens today. I would much prefer that Superuser 'a' reaches WITH CONNECTION LIMIT for user 'a' and superuser 'b' can get in to see what is happening. > > Slony in particular does not need more than N connections but does > > require being a super user. > > Maybe someone should look into enabling slony to not run as a > superuser? > > Have a nice day, -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 15:07 +0200, Csaba Nagy wrote: > On Mon, 2006-07-31 at 15:00, Martijn van Oosterhout wrote: > > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > > > It appears that the superuser does not have connection limit > > > enforcement. I think this should be changed. > > > > So if some admin process goes awry and uses up all the connection > > slots, how does the admin get in to see what's happening? If there's a > > limit you're not really superuser, are you? > > I thought there is a limit for super-users too... citation from: > http://www.postgresql.org/docs/8.1/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS Sorry for not being more specific. I was speaking about ALTER ROLE WITH CONNECTION LIMIT. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 09:06 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > It appears that the superuser does not have connection limit > > enforcement. I think this should be changed. > > If you're superuser, you are not subject to access restrictions, > by definition. I cannot imagine any scenario under which the > above would be a good idea. (Hint: it would be more likely to > lock out manual admin connections than Slony.) If you don't want an admin user to have a connection limit, give them "-1" or no connection limit. Anyway, you're right that Slony should not require superuser status but at the moment that is rather tricky to accomplish since it wants to muck about in the system catalogues, use pg_cancel_backend, among other things. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Connection limit and Superuser
It appears that the superuser does not have connection limit enforcement. I think this should be changed. Slony in particular does not need more than N connections but does require being a super user. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] extension for sql update
On Sun, 2006-07-30 at 20:20 -0400, Robert Treat wrote: > On Thursday 27 July 2006 09:28, Bruce Momjian wrote: > > Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > Tom Lane wrote: > > > >> UPDATE mytab SET (foo, bar, baz) = > > > >> (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); > > > > > > > > That UPDATE example is interesting because I remember when using > > > > Informix that I had to do a separate SELECT statement for each UPDATE > > > > column I wanted to update. I didn't realize that you could group > > > > columns and assign them from a single select --- clearly that is a > > > > powerful syntax we should support some day. > > > > > > No question. The decision at hand is whether we want to look like > > > we support it, when we don't yet. I'd vote not, because I think the > > > main use-case for the row-on-the-left syntax is exactly this, and > > > so I fear people will just get frustrated if they see it in the > > > syntax synopsis and try to use it. > > > > I'm not a big fan of implementing partial solutions (remember "left-joins are > not implemented messages" :-) way back when) , however in my experience with > this form of the update command, the primary usage is not to use a subselect > to derive the values, but to make it easier to generate sql, using a single I disagree. UPDATE mytab SET (foo, bar, baz) =(SELECT ...) is the specifications way of doing an update with a join. That is its primary purpose. UPDATE ... FROM is a PostgreSQL alternative to the above. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better name/syntax for "online" index creation
Sorry, hit send too quickly. NOLOCK is kinda like NOWAIT, except implies that the command will not take a strong lock instead of stating that it will not wait for one. On Mon, 2006-07-24 at 11:20 -0400, Rod Taylor wrote: > > SHARED CREATE INDEX > > > Comments? > > CREATE [UNIQUE] INDEX [WITH NOLOCK] ON ... > > -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for "online" index creation
> SHARED CREATE INDEX > Comments? CREATE [UNIQUE] INDEX [WITH NOLOCK] ON ... -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Three weeks left until feature freeze
On Thu, 2006-07-13 at 11:03 -0400, Jonah H. Harris wrote: > On 7/13/06, Lukas Smith wrote: > > However I do think that PostgreSQL is missing out in > > getting new users aboard that are in the early stages > > of evalutation and simply only consider features that > > they get along with a default installation (mostly due > > to lack of better knowledge about places like pgfoundry). > > This is my point exactly. As with many things, we keep skirting the > real issue by going with an "improve the smaller component" approach > such as "promote pgfoundry more". I have never seen this approach > work, but maybe someone has an example of another OSS project that has > successfully excluded major components like this? Personally, I prefer the Gnome approach. Most components are developed and bundled independently but once the code meets certain stability and usability requirements the component is promoted to the standard website with standard documentation, bug reporting, download locations, etc. On PostgreSQL.org, aside from the "Downloads" tab it is very difficult to find these items. PGFoundry does not attempt to differentiate between the state of projects. Top downloads is the closest to this. XML based docbook can easily suck in documentation from multiple remote sources (when available -- substituted when not available) for a single documentation build via XMLIncludes. This allows for PL/Java chapter in the standard documentation set online. PostgreSQL.org "Support" could pretty easily link to the various locations for bug reports -- Bugzilla makes component selection a common requirement. A slight restructuring of the FTP tree should probably be made. It's currently very easy to find the main pgsql, pgadmin and odbc components. Finding pl/java (what the heck is that gborg or pgfoundry project?) is pretty difficult. The only real tricky part is defining what a plugin or addon application such as pgadmin needs to be considered production ready. This will relate to testing practices, documentation, code quality, ongoing maintenance, and expected supported lifetime. For lifetime, if it was released for Core 7.3 should still work with 7.3 today or old versions should still receive security and bug fixes. -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_terminate_backend idea
On Tue, 2005-06-21 at 23:34 -0400, Tom Lane wrote: > Bruce Momjian writes: > > Tom Lane wrote: > >> In any case the correct way to solve the problem is to find out what's > >> being left corrupt by SIGTERM, rather than install more messiness in > >> order to avoid facing the real issue ... > > > I am confused. Are you talking about the client SIGTERM or the server? > > I am talking about Rod Taylor's reports that SIGTERM'ing individual > backends tends to lead to "lock table corrupted" crashes awhile later. > Now, I've been playing the part of Chicken Little on this for awhile, > but seeing an actual report of problems from the field certainly > strengthens my feelings about it. Bringing this thread back to life. I have not seen a lock table corruption issue with SIGTERM in 8.1 on Solaris/Sun IV, Linux/AMD64, or Linux/Intel. I don't recall seeing one on 8.0.3 either though I'm pretty sure there were several on 8.0.1. There are times when locks for a process hang around for a few minutes before getting cleared. I don't recall whether they were ungranted table locks or entries waiting on a transaction ID lock, but the source was Slony and a large pg_listener structure with more than 2 pages (yes, pages not tuples). I have also seen processes refusing to acknowledge the signal and exit during btree index builds, but that's not a data corruption issue. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] vacuum, performance, and MVCC
On Thu, 2006-06-22 at 13:56 -0400, Mark Woodward wrote: > >> > You mean systems that are designed so exactly, that they can't take > >> 10% > >> > performance change ? > >> > >> No, that's not really the point, performance degrades over time, in one > >> minute it degraded 10%. > >> > >> The update to session ratio has a HUGE impact on PostgreSQL. If you have > >> a > >> thousand active sessions, it may take a minute to degrade 10% assuming > >> some level of active vs operations per session per action. > > > > So don't do an update. Multiple updates to the same row block anyway > > which is generally not something you want anyway. > > The example is a very active web site, the flow is this: > > query for session information > process HTTP request > update session information > > This happens for EVERY http request. Chances are that you won't have > concurrent requests for the same row, but you may have well over 100 HTTP > server processes/threads answering queries in your web server farm. > > > > > If you INSERT into multiple partitions (by time -- say one per minute) > > and TRUNCATE periodically (30 minute old partitions for 30 minute > > expiry) it works much better. Expiring the session is quite fast as well > > since they'll go away with the truncate. > > > > Index on sessionid and time and grab the row with the most recent time. > > I doubt that that approach (1) answers the problem or (2) would be more > efficient. Depends on what else you have going on in the background. If you can vacuum constantly and have the garbage cleaned up, it may not be. If you have a long running transaction (pg_dump, vacuum?, report query, ...) in the background then it can make a pretty good difference last time I measured. If this is the only thing that the database has and vacuum still isn't able to keep up, who knows you might find advantages to being able to truncate periodically instead of a constant vacuum. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] vacuum, performance, and MVCC
On Thu, 2006-06-22 at 13:42 -0400, Jonah H. Harris wrote: > On 6/22/06, Rod Taylor <[EMAIL PROTECTED]> wrote: > > If you INSERT into multiple partitions (by time -- say one per minute) > > and TRUNCATE periodically (30 minute old partitions for 30 minute > > expiry) it works much better. Expiring the session is quite fast as well > > since they'll go away with the truncate. > > Forgive me if this sounds rude because I'm not meaning it as such, but > this suggestion sounds like a MySQL-ish hack. No doubt it would work, > but should an application-writer have to incur the cost of writing > something different because the database can't handle it? I remember > having to write subselect code in PHP because MySQL couldn't do it... > not what I would call a *solution* by any mean. Application writer doesn't need to do anything. That's what rules, views, etc. are for. The only snag is that PostgreSQL doesn't 'eliminate' a partition by itself, so the truncate is a manual requirement. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] vacuum, performance, and MVCC
> > You mean systems that are designed so exactly, that they can't take 10% > > performance change ? > > No, that's not really the point, performance degrades over time, in one > minute it degraded 10%. > > The update to session ratio has a HUGE impact on PostgreSQL. If you have a > thousand active sessions, it may take a minute to degrade 10% assuming > some level of active vs operations per session per action. So don't do an update. Multiple updates to the same row block anyway which is generally not something you want anyway. If you INSERT into multiple partitions (by time -- say one per minute) and TRUNCATE periodically (30 minute old partitions for 30 minute expiry) it works much better. Expiring the session is quite fast as well since they'll go away with the truncate. Index on sessionid and time and grab the row with the most recent time. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] vacuum, performance, and MVCC
> > Here we have for example some tables which are frequently updated but > > contain >100 million rows. Vacuuming that takes hours. And the dead row > > candidates are the ones which are updated again and again and looked up > > frequently... > > This demonstrates that "archival" material and "active" data should be > kept separately. > > They have different access patterns; kludging them into the same table > turns out badly. Rightfully it should be up to the database engine to ensure that both of these patterns work against the same structure. Splitting up the data for their access patterns is the job of partitions (hidden from the end user preferably). Same table good, same partition and possible same table space is bad. -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] postgresql and process titles
> > I did have dbt2 pretty close to functional on FreeBSD a year ago but > > it's probably gone back into linuxisms since then. > > :( > > I won't have the chance to work on this further for another 2 months, > but if you have patches I could see about picking up on them when I > get back. Everything has been applied to the dbt2 tree. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] postgresql and process titles
On Tue, 2006-06-13 at 14:18 -0400, Kris Kennaway wrote: > On Tue, Jun 13, 2006 at 12:29:14PM -0500, Jim C. Nasby wrote: > > > Unless supersmack has improved substantially, you're unlikely to find > > much interest. Last I heard it was a pretty brain-dead benchmark. DBT2/3 > > (http://sourceforge.net/projects/osdldbt) is much more realistic (based > > on TPC-C and TPC-H). > > Have you tried to compile this on FreeBSD? It looks like it (dbt1 at > least) will need a moderate amount of hacking - there are some Linux > assumptions in the source and the configure script makes assumptions > about where things are installed that cannot be overridden on the > commandline. I did have dbt2 pretty close to functional on FreeBSD a year ago but it's probably gone back into linuxisms since then. -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CSV mode option for pg_dump
On Mon, 2006-06-12 at 16:28 -0400, Bill Bartlett wrote: > Can't -- the main production database is over at a CoLo site with access > only available via SSH, and tightly-restricted SSH at that. Generally > one of the developers will SSH over to the server, pull out whatever > data is needed into a text file via psql or pg_dump, scp the file(s) > back here and send them to the user. I don't get it. If you can use psql then you already have csv support. psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv > > -Original Message- > > From: Joshua D. Drake [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 12, 2006 4:15 PM > > To: Bill Bartlett > > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > > > > Bill Bartlett wrote: > > > Here's me speaking up -- I'd definitely use it! As a > > quick way to pull > > > data into Excel to do basic reports or analysis, a CSV > > format would be > > > great. > > > > Why not just use ODBC? > > > > Joshua D. Drake > > -- > > > > === The PostgreSQL Company: Command Prompt, Inc. === > >Sales/Support: +1.503.667.4564 || 24x7/Emergency: > > +1.800.492.2240 > >Providing the most comprehensive PostgreSQL solutions > > since 1997 > > http://www.commandprompt.com/ > > > > > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Extended SERIAL parsing
> The condition (column->is_serial && column->force_default) > can help enforcing GENERATED ALWAYS at INSERT time > and can also help fixing the two TODO entries about SERIAL. You will need to include the insert components of the spec which allow for overriding GENERATED ALWAYS during an INSERT and extend that to COPY and teach pg_dump how to use them. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to avoid transaction ID wrap
On Tue, 2006-06-06 at 13:53 -0400, Christopher Browne wrote: > Clinging to sanity, [EMAIL PROTECTED] (Hannu Krosing) mumbled into her beard: > > Ühel kenal päeval, T, 2006-06-06 kell 08:42, kirjutas Mark Woodward: > >> OK, here's my problem, I have a nature study where we have about 10 video > >> cameras taking 15 frames per second. > >> For each frame we make a few transactions on a PostgreSQL database. > >> We want to keep about a years worth of data at any specific time. > > > > partition by month, then you have better chances of removing old data > > without causing overload/data loss; > > It's certainly worth something to be able to TRUNCATE an elderly > partition; that cleans things out very nicely... With one potential snafu -- it blocks new SELECTs against the parent table while truncate runs on the child (happens with constraint exclusion as well). If your transactions are short then it won't be an issue. If you have mixed length transactions (many short which the occasional long select) then it becomes tricky since those short transactions will be blocked. > >> We have triggers that fire is something interesting is found on insert. > >> We want this thing to run for a log time. > >> From the numbers, you can see the PostgreSQL database is VERY loaded. > >> Running VACUUM may not always be possible without losing data. > > > > why ? just run it with very friendly delay settings. > > "Friendly delay settings" can have adverse effects; it is likely to > make vacuum run on the order of 3x as long, which means that if you > have a very large table that takes 12h to VACUUM, "vacuum delay" will > increase that to 36h, which means you'll have a transaction open for > 36h. Sounds like this is almost strictly inserts and selects though. If there is limited garbage collection (updates, deletes, rollbacks of inserts) required then it isn't all that bad. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More thoughts about planner's cost estimates
> One objection to this is that after moving "off the gold standard" of > 1.0 = one page fetch, there is no longer any clear meaning to the > cost estimate units; you're faced with the fact that they're just an > arbitrary scale. I'm not sure that's such a bad thing, though. For > instance, some people might want to try to tune their settings so that > the estimates are actually comparable to milliseconds of real time. Any chance that the correspondence to time could be made a part of the design on purpose and generally advise people to follow that rule? If we could tell people to run *benchmark* and use those numbers directly as a first approximation tuning, it could help quite a bit for people new to PostgreSQL experiencing poor performance. effective_cache_size then becomes essentially the last hand-set variable for medium sized installations. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
On Fri, 2006-05-19 at 09:11 -0700, Joshua D. Drake wrote: > Martijn van Oosterhout wrote: > > On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote: > >> The reality is that MySQL is widely supported by some very, shall we say, > >> "interesting" open source projects and using these products with > >> PostgreSQL would be a plus. > > > > The biggest headache I find with using postgres is that various GPL > > licenced programs have trouble directly shipping postgresql support > > because of our use of OpenSSL. Each and every one of those program > > needs to add an exception to their licence for distributors to > > distribute postgresql support. > > Why would that be the case... OpenSSL and PostgreSQL both are BSD > licensed... Am I missing something? OpenSSL is not the 3 clause BSD license, it also includes a number of advertising clauses that the GPL has never liked -- GPL must not be modified for derivatives but the advertising clauses are in addition to the GPL, so it must be modified for the combination. Exceptions exist in the GPL for libraries and tools included in the operating system and this is enough in most cases. GPL applications on Windows may have problems. http://www.openssl.org/support/faq.html#LEGAL2 2. Can I use OpenSSL with GPL software? On many systems including the major Linux and BSD distributions, yes (the GPL does not place restrictions on using libraries that are part of the normal operating system distribution). On other systems, the situation is less clear. Some GPL software copyright holders claim that you infringe on their rights if you use OpenSSL with their software on operating systems that don't normally include OpenSSL. If you develop open source software that uses OpenSSL, you may find it useful to choose an other license than the GPL, or state explicitly that "This program is released under the GPL with the additional exemption that compiling, linking, and/or using OpenSSL is allowed." If you are using GPL software developed by others, you may want to ask the copyright holder for permission to use their software with OpenSSL. OpenSSL License * 1. Redistributions of source code must retain the above copyright *notice, this list of conditions and the following disclaimer. * * 2. Redistributions in binary form must reproduce the above copyright *notice, this list of conditions and the following disclaimer in *the documentation and/or other materials provided with the *distribution. * * 3. All advertising materials mentioning features or use of this *software must display the following acknowledgment: *"This product includes software developed by the OpenSSL Project *for use in the OpenSSL Toolkit. (http://www.openssl.org/)" * * 4. The names "OpenSSL Toolkit" and "OpenSSL Project" must not be used to *endorse or promote products derived from this software without *prior written permission. For written permission, please contact *[EMAIL PROTECTED] * * 5. Products derived from this software may not be called "OpenSSL" *nor may "OpenSSL" appear in their names without prior written *permission of the OpenSSL Project. * * 6. Redistributions of any form whatsoever must retain the following *acknowledgment: *"This product includes software developed by the OpenSSL Project *for use in the OpenSSL Toolkit (http://www.openssl.org/)" > > I'm thinking particularly of FreeRadius but there are others. More than > > once I thought while waiting for stuff to compile: if I'd chosen mysql > > I'd be done by now... > > > > Have a nice day, -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
> Actually, I suspect in most cases it won't matter; I don't think people > make a habit of trying to sort their entire database. :) But we'd want > to protect for the oddball cases... yech. I can make query result sets that are far larger than the database itself. create table fat_table_with_few_tuples(fat_status_id serial primary key, fat_1 text, fat_2 text); create table thin_table_with_many_tuples(fat_status_id integer references fat_table_with_few_tuples, thin_1 integer, thin_2 integer); SELECT * FROM thin_table_with_many_tuples NATURAL JOIN fat_table_with_few_tuples order by fat_1, thin_1, thin_2, fat_2; I would be asking the folks trying to use PostgreSQL for data warehousing what their opinion is. A few fact tables in an audit query could easily result in a very large amount of temporary diskspace being required. -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
On Tue, 2006-05-16 at 11:53 -0400, Andrew Dunstan wrote: > Bort, Paul wrote: > >> Compressed-filesystem extension (like e2compr, and I think either > >> Fat or NTFS) can do that. > >> > >> > > > > Windows (NT/2000/XP) can compress individual directories and files under > > NTFS; new files in a compressed directory are compressed by default. > > > > So if the 'spill-to-disk' all happened in its own specific directory, it > > would be trivial to mark that directory for compression. > > > > I don't know enough Linux/Unix to know if it has similar capabilities. > Or would want to ... > > I habitually turn off all compression on my Windows boxes, because it's > a performance hit in my experience. Disk is cheap ... Disk storage is cheap. Disk bandwidth or throughput is very expensive. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Number of dimensions of an array parameter
> thhal=# CREATE DOMAIN twodims as int[][]; > CREATE DOMAIN While still not perfect, you can use a CHECK constraint on the domain to enforce dimension. It's not perfect because domain constraints are not enforced in all locations in versions earlier than 8.2. Adding extra explicit casts can often work around that though. ru=# create domain twodims as int[][] check(array_dims(value) = '[1:2][1:2]'); ru=# select array_dims('{{{1,2},{3,4}},{{5,3},{9,9}}}'::twodims); ERROR: value for domain twodims violates check constraint "twodims_check" ru=# select array_dims('{{1,2},{3,4}}'::twodims); array_dims [1:2][1:2] (1 row) If you want to be fancy, use something like this: check(array_dims(value) ~ '^[1:\\d+][1:\\d+]$'); -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 18:53 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > On Fri, 2006-05-05 at 16:11 -0400, Tom Lane wrote: > >> Hm. I wonder if there are any uses of "exit(1)" in the Slony triggers. > > > It doesn't appear so. It does have this though: > > Well, a SIGTERM would have resulted in a bleat in the postmaster log. > The striking thing about your log is that the backend went down without > saying a word; which would be understandable if it had crashed (eg SEGV > or kill -9) but then the postmaster would have seen some other exit > status. I'm fairly certain there are no paths in the standard backend > code that will exit(1) without any attempt to print a message. That's > why I'm wondering about add-ons. Add-ons are slim. Slony. We don't have any C based functions and only a few plpgsql functions in that DB. I did trim out a ton of autovacuum log entries (it likes to log once a minute) but I don't see anything interesting in that area nor autovac the pid that exited. My knowledge of signal handling is pretty basic. Any chance that multiple SIGTERMs could have caused it to avoid the logging? -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 16:11 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > % 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171) > > exited with exit code 1 > > Hm. I wonder if there are any uses of "exit(1)" in the Slony triggers. It doesn't appear so. It does have this though: Datum _Slony_I_killBackend(PG_FUNCTION_ARGS) { int32 pid; int32 signo; text *signame; if (!superuser()) elog(ERROR, "Slony-I: insufficient privilege for killBackend"); pid = PG_GETARG_INT32(0); signame = PG_GETARG_TEXT_P(1); if (VARSIZE(signame) == VARHDRSZ + 4 && memcmp(VARDATA(signame), "NULL", 0) == 0) { signo = 0; } else if (VARSIZE(signame) == VARHDRSZ + 4 && memcmp(VARDATA(signame), "TERM", 0) == 0) { signo = SIGTERM; } else { elog(ERROR, "Slony-I: unsupported signal"); } if (kill(pid, signo) < 0) PG_RETURN_INT32(-1); PG_RETURN_INT32(0); } -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 15:10 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > On Fri, 2006-05-05 at 14:31 -0400, Tom Lane wrote: > >> Unless you had an actual backend crash, that's not an adequate > >> explanation. Transaction abort does clean up created files. > > > The only thing I can come up with is that perhaps someone forcefully > > gave it a kick. SIGTERM is a necessary action once in a while to unwedge > > a stuck db connection (killing the client script doesn't always get it > > immediately). > > SIGTERM wouldn't cause that either. I hope your people are not in the > habit of using "kill -9"? Command line records, etc. are not available, but I did track down a a snippet of logs from the backups (daily log rotation). Sorry, I didn't realize there were backups initially -- it's unusual. Appears it did crash or get killed in some way "exited with exit code 1". It's a temp DB to try a different database encoding (prep for 8.1 upgrade) with production data. Is there something you would like me to try doing in an attempt to reproduce? Preferably with a smaller structure. The truncate would have happened as part of the prepareTableForCopy() call. slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOG: 0: duration: 30526368.316 ms statement: select "_ssrep".prepareTableForCopy(1010); copy from stdin; slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOCATION: exec_simple_query, postgres.c:1103 slony%ssdb 10171 4621947 2006-05-02 05:09:40 EDTLOCATION: exec_simple_query, postgres.c:1103 slony%ssdb 10181 0 2006-05-02 15:32:06 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10181 0 2006-05-02 15:32:06 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10154 0 2006-05-02 15:32:06 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10154 0 2006-05-02 15:32:06 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10173 0 2006-05-02 16:30:53 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10173 0 2006-05-02 16:30:53 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 10755 0 2006-05-02 16:30:53 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 10755 0 2006-05-02 16:30:53 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 300 0 2006-05-02 16:55:18 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 300 0 2006-05-02 16:55:18 EDTLOCATION: SocketBackend, postgres.c:295 slony%ssdb 301 0 2006-05-02 16:55:18 EDTLOG: 08P01: unexpected EOF on client connection slony%ssdb 301 0 2006-05-02 16:55:18 EDTLOCATION: SocketBackend, postgres.c:295 % 1960 2006-05-02 17:03:19 EDTLOG: 0: server process (PID 10171) exited with exit code 1 % 1960 2006-05-02 17:03:19 EDTLOCATION: LogChildExit, postmaster.c:2416 % 1960 2006-05-02 17:03:19 EDTLOG: 0: terminating any other active server processes % 1960 2006-05-02 17:03:19 EDTLOCATION: HandleChildCrash, postmaster.c:2306 % 1960 2006-05-02 17:03:19 EDTLOCATION: HandleChildCrash, postmaster.c:2306 % 1960 2006-05-02 17:03:19 EDTLOG: 0: all server processes terminated; reinitializing % 1960 2006-05-02 17:03:19 EDTLOCATION: reaper, postmaster.c:2206 < snip connection attempts > % 5826 2006-05-02 17:03:22 EDTLOG: 0: database system was interrupted at 2006-05-02 16:06:20 EDT % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4374 % 5826 2006-05-02 17:03:22 EDTLOG: 0: checkpoint record is at 59/E0B56920 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4442 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo record is at 59/E0B56920; undo record is at 0/0; shutdown FALSE % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4469 % 5826 2006-05-02 17:03:22 EDTLOG: 0: next transaction ID: 4863932; next OID: 1441853 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4472 % 5826 2006-05-02 17:03:22 EDTLOG: 0: next MultiXactId: 1; next MultiXactOffset: 0 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4475 % 5826 2006-05-02 17:03:22 EDTLOG: 0: database system was not properly shut down; automatic recovery in progress % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4532 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo starts at 59/E0B56970 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4569 % 5826 2006-05-02 17:03:22 EDTLOG: 0: record with zero length at 59/E0E429B8 % 5826 2006-05-02 17:03:22 EDTLOCATION: ReadRecord, xlog.c:2764 % 5826 2006-05-02 17:03:22 EDTLOG: 0: redo done at 59/E0E42988 % 5826 2006-05-02 17:03:22 EDTLOCATION: StartupXLOG, xlog.c:4627 % 5826 2006-05-02 17:03:23 EDTLOG: 0: database system is ready % 5826 2006-05-02 17:03:23 EDTLOCATION: StartupXLOG, xlog.c:4821 % 5826 2006-05-02 17:03:23 EDTLOG: 0: transaction ID wrap limit is 1073749769, limited by
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 14:31 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > At some point it must have failed in copying the data across, aborted, > > and restarted. > > Unless you had an actual backend crash, that's not an adequate > explanation. Transaction abort does clean up created files. The only reason I noticed is because pg_database_size didn't match sum(pg_total_relation_size()) and was investigating what I thought was a bug in one of those functions. I'm afraid we don't have all of the monitoring, logging, and change control bits hooked up to the non-production DBs, so that is pretty much all I have other than conjecture. The only thing I can come up with is that perhaps someone forcefully gave it a kick. SIGTERM is a necessary action once in a while to unwedge a stuck db connection (killing the client script doesn't always get it immediately). Slony holds open a transaction on the master while reindexing the slave, so perhaps someone thought the slave needed help. Making a copy of the master takes several weeks. They may have killed slony, found the statements still working away, SIGTERM'd them both, then restarted slony. It wouldn't be an unusual pattern of events, particularly since they've not been taught about pg_cancel_backend() yet (no 8.1 training). How about this? BEGIN; TRUNCATE; COPY; REINDEX ; pg_class references old files. New files in their aborted state are left behind? -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1.3 and unused files
On Fri, 2006-05-05 at 14:09 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Am I correct in the thought that the various files listed below are not > > used by the database and can be safely removed? There were no other > > active db connections when I issued this command. > > > I think truncate (Slony) left them behind. > > I don't particularly like the casual assumption that truncate is broken. > If I were you I'd be looking harder for a plausible explanation about > where these files came from, especially seeing how large they are. > Have you tried dumping the file contents to see if the data looks > recognizable at all? Hardware is perfectly functional and has been for about 18 months in production with 8.0.x. It is a completely new 8.1 database and Slony is the only entity that has been working in it. There are not very many possibilities. I'm fairly confident I know exactly which table they are/were a part of. 1434984 is the table data, 1434986 is the primary key of the table (only index), and 1434985 is probably the toast structure. The structure have different relfilenode values and valid data at this time. At some point it must have failed in copying the data across, aborted, and restarted. So it would have been something like this: BEGIN; TRUNCATE; "decouple indexes -- ask Jan"; COPY; "recouple indexes"; REINDEX ; BEGIN; TRUNCATE; "decouple indexes -- ask Jan"; COPY; "recouple indexes"; REINDEX; COMMIT; -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 8.1.3 and unused files
Am I correct in the thought that the various files listed below are not used by the database and can be safely removed? There were no other active db connections when I issued this command. I think truncate (Slony) left them behind. ssdb=# select file from pg_ls_dir('base/'|| (select oid from pg_database where datname = 'ssdb')) as tab(file) where file !~ '\\..*$' and file not in (select relfilenode from pg_class) and file not in ('PG_VERSION', 'pgsql_tmp'); file - 1434986 1434984 1434985 (3 rows) [EMAIL PROTECTED] 16384]# ls -la 143498[456]* -rw--- 1 rbt sysadmin 1073741824 May 1 20:56 1434984 -rw--- 1 rbt sysadmin 1073741824 May 1 21:11 1434984.1 -rw--- 1 rbt sysadmin 1073741824 May 1 23:21 1434984.10 -rw--- 1 rbt sysadmin 1073741824 May 1 23:36 1434984.11 -rw--- 1 rbt sysadmin 1073741824 May 1 23:50 1434984.12 -rw--- 1 rbt sysadmin 1073741824 May 2 00:06 1434984.13 -rw--- 1 rbt sysadmin 1073741824 May 2 00:23 1434984.14 -rw--- 1 rbt sysadmin 1073741824 May 2 00:39 1434984.15 -rw--- 1 rbt sysadmin 1073741824 May 2 00:57 1434984.16 -rw--- 1 rbt sysadmin 1073741824 May 2 01:14 1434984.17 -rw--- 1 rbt sysadmin 1073741824 May 2 01:31 1434984.18 -rw--- 1 rbt sysadmin 1073741824 May 2 01:50 1434984.19 -rw--- 1 rbt sysadmin 1073741824 May 1 21:25 1434984.2 -rw--- 1 rbt sysadmin 1073741824 May 2 02:07 1434984.20 -rw--- 1 rbt sysadmin 1073741824 May 2 02:23 1434984.21 -rw--- 1 rbt sysadmin 1073741824 May 2 02:41 1434984.22 -rw--- 1 rbt sysadmin 1073741824 May 2 02:55 1434984.23 -rw--- 1 rbt sysadmin 1073741824 May 2 03:09 1434984.24 -rw--- 1 rbt sysadmin 1073741824 May 2 03:24 1434984.25 -rw--- 1 rbt sysadmin 1073741824 May 2 03:37 1434984.26 -rw--- 1 rbt sysadmin 1073741824 May 2 03:53 1434984.27 -rw--- 1 rbt sysadmin 1073741824 May 2 04:09 1434984.28 -rw--- 1 rbt sysadmin 1073741824 May 2 04:24 1434984.29 -rw--- 1 rbt sysadmin 1073741824 May 1 21:40 1434984.3 -rw--- 1 rbt sysadmin 1073741824 May 2 04:40 1434984.30 -rw--- 1 rbt sysadmin 1073741824 May 2 04:56 1434984.31 -rw--- 1 rbt sysadmin 990912512 May 2 05:09 1434984.32 -rw--- 1 rbt sysadmin 1073741824 May 1 21:54 1434984.4 -rw--- 1 rbt sysadmin 1073741824 May 1 22:08 1434984.5 -rw--- 1 rbt sysadmin 1073741824 May 1 22:23 1434984.6 -rw--- 1 rbt sysadmin 1073741824 May 1 22:36 1434984.7 -rw--- 1 rbt sysadmin 1073741824 May 1 22:52 1434984.8 -rw--- 1 rbt sysadmin 1073741824 May 1 23:07 1434984.9 -rw--- 1 rbt sysadmin8192 May 1 20:40 1434985 -rw--- 1 rbt sysadmin 1073741824 May 2 11:27 1434986 -rw--- 1 rbt sysadmin 1073741824 May 2 11:39 1434986.1 -rw--- 1 rbt sysadmin 121733120 May 2 16:53 1434986.10 -rw--- 1 rbt sysadmin 1073741824 May 2 11:56 1434986.2 -rw--- 1 rbt sysadmin 1073741824 May 2 12:15 1434986.3 -rw--- 1 rbt sysadmin 1073741824 May 2 12:43 1434986.4 -rw--- 1 rbt sysadmin 1073741824 May 2 13:15 1434986.5 -rw--- 1 rbt sysadmin 1073741824 May 2 13:53 1434986.6 -rw--- 1 rbt sysadmin 1073741824 May 2 14:35 1434986.7 -rw--- 1 rbt sysadmin 1073741824 May 2 15:38 1434986.8 -rw--- 1 rbt sysadmin 1073741824 May 2 16:53 1434986.9 -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autovacuum logging, part deux.
On Thu, 2006-05-04 at 11:25 -0500, Larry Rosenman wrote: > Rod Taylor wrote: > > I don't know about anyone else, but the only time I look at that mess > > is to find poor tuple/table or tuple/index ratios and other > > indications that vacuum isn't working as well as it should be. > > > > How about this instead: > > > > Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned > > up) was more than 2 times the autovacuum_vacuum_scale_factor listed in > > postgresql.conf. This means autovacuum isn't keeping up to what you > > want it to. > > > > Another interesting case would be a large amount of empty space in the > > index or table (say 3x autovacuum_vacuum_scale_factor). This may > > indicate unnecessary bloat and something to fix. > > > > Aside from that, the raw numbers don't really interest me. > > > > Does anyone think we should have a stats view for the last vacuum stats > for each table? This would actually suit me better as it would be trivial to plug into a monitoring system with home-brew per table thresholds at that point. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autovacuum logging, part deux.
I don't know about anyone else, but the only time I look at that mess is to find poor tuple/table or tuple/index ratios and other indications that vacuum isn't working as well as it should be. How about this instead: Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned up) was more than 2 times the autovacuum_vacuum_scale_factor listed in postgresql.conf. This means autovacuum isn't keeping up to what you want it to. Another interesting case would be a large amount of empty space in the index or table (say 3x autovacuum_vacuum_scale_factor). This may indicate unnecessary bloat and something to fix. Aside from that, the raw numbers don't really interest me. On Thu, 2006-05-04 at 14:46 +, Chris Browne wrote: > [EMAIL PROTECTED] ("Larry Rosenman") writes: > > Gentlepeople, > > Now that the patch is out for keeping the last > > autovacuum/vacuum/analyze/autoanalyze > > timestamp in the stats system is pending, what's the consensus view on > > what, if any, > > logging changes are wanted for autovacuum? > > > > I have the time and inclination to cut code quickly for it. > > It would be Really Nice if it could draw in the verbose stats as to > what the VACUUM did... > > e.g. - to collect some portion (INFO? DETAIL? I'm easy :-)) of the > information that PostgreSQL generates at either INFO: or DETAIL: > levels. > > /* [EMAIL PROTECTED]/dba2 vacdb=*/ vacuum verbose analyze vacuum_requests; > INFO: vacuuming "public.vacuum_requests" > INFO: index "vacuum_requests_pkey" now contains 2449 row versions in 64 pages > DETAIL: 3 index pages have been deleted, 3 are currently reusable. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: index "vr_priority" now contains 0 row versions in 19 pages > DETAIL: 16 index pages have been deleted, 16 are currently reusable. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: "vacuum_requests": found 0 removable, 2449 nonremovable row versions > in 65 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 2809 unused item pointers. > 0 pages are entirely empty. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: vacuuming "pg_toast.pg_toast_95167460" > INFO: index "pg_toast_95167460_index" now contains 0 row versions in 1 pages > DETAIL: 0 index pages have been deleted, 0 are currently reusable. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: "pg_toast_95167460": found 0 removable, 0 nonremovable row versions in > 0 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 0 unused item pointers. > 0 pages are entirely empty. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > INFO: analyzing "public.vacuum_requests" > INFO: "vacuum_requests": 65 pages, 2449 rows sampled, 2449 estimated total > rows > VACUUM > -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sat, 2006-04-29 at 23:15 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Do both. Return SERIAL to being a macro and implement the SQL IDENTITY > > construct as the black box version. > > Doesn't SQL IDENTITY have a number of properties that are significantly > different from serial/nextval? I wasn't really volunteering to > implement a large new feature to make this happen ;-) Yes. Including a few really nice properties and a really ugly workaround. I didn't mean to imply that you should write it. I just meant that the spec already has an automatic sequence generator which is black-box. If SERIAL is going to be kept long term, then it should be the macro version so it doesn't appear too duplicated. > Also, I'm not sure how "black boxy" it can be without buying right back > into the pg_dump problems. pg_dump has to be able to see what's inside, > I think. Not sure which pg_dump problem you're referring to. A fully black box generator would completely hide the column default and sequence. Pg_dump and users can create and modify foreign keys without knowledge of the trigger implementation, the same would be true here. For the spec, the ugly workaround is "OVERRIDING SYSTEM VALUE" which allows a table owner to override the ALWAYS GENERATE designation -- essentially the same as a trigger bypass switch for bulk data loads. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sat, 2006-04-29 at 17:54 -0400, Tom Lane wrote: > In some recent activity on the patches list about responding to bug #2073, > http://archives.postgresql.org/pgsql-bugs/2005-11/msg00303.php > we've been discussing various possible tweaks to the behavior of dropping > or modifying a serial column. The hacks involved with SERIAL seem to me > to be getting uglier and uglier, and I think it's time to take a step > back and consider what we really want SERIAL to act like. > > It seems to me there are two basic philosophies at war here: > > 1. A serial column is a "black box" that you're not supposed to muck with > the innards of. > 2. A serial declaration is just a "macro" for setting up a sequence and a > column default expression. This was the original viewpoint and indeed is > still what it says in the documentation: > Comments, other opinions? Do both. Return SERIAL to being a macro and implement the SQL IDENTITY construct as the black box version. CREATE TABLE foo (bar integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY); INSERT ... RETURNS needs to be implemented before SERIAL can become a black box. Until that time we will still need some knowledge of the sequence involved. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Left joins and inheritance (table partitioning)
On Tue, 2006-04-04 at 23:50 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > I've recently been playing with table partitioning limitations. Turning > > over a large volume of data in inherited structures in a live > > environment, and have run into a couple of snags in the planner. > > > The first is that LEFT JOIN will always do a sequential scan on all > > inherited tables. > > Try it in CVS HEAD. Indeed, much better. Sorry about the noise. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Left joins and inheritance (table partitioning)
I've recently been playing with table partitioning limitations. Turning over a large volume of data in inherited structures in a live environment, and have run into a couple of snags in the planner. The first is that LEFT JOIN will always do a sequential scan on all inherited tables. The second is that IN (1,4,6) works very differently than IN (SELECT id FROM tab) when "tab" contains the values 1, 4, and 6. I'm not surprised a straight left join failed, but I was surprised that IN failed to use an index with enable_seqscan = off. My fallback plan is to simply create a view and replace it to point to the correct data segment when changes occur. BEGIN; CREATE TABLE key (keyword_id serial PRIMARY KEY); INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; INSERT INTO key DEFAULT VALUES; CREATE TABLE key2 (keyword_id integer PRIMARY KEY); INSERT INTO key2 VALUES (1); INSERT INTO key2 VALUES (6); INSERT INTO key2 VALUES (8); ANALYZE key2; CREATE TABLE foo ( keyword_id integer PRIMARY KEY REFERENCES key); CREATE TABLE foo2 ( PRIMARY KEY (keyword_id) , FOREIGN KEY (keyword_id) REFERENCES KEY ) INHERITS (foo); INSERT INTO foo2 VALUES (1); INSERT INTO foo2 VALUES (2); INSERT INTO foo2 VALUES (3); INSERT INTO foo2 VALUES (4); INSERT INTO foo2 VALUES (5); INSERT INTO foo2 VALUES (6); INSERT INTO foo2 VALUES (7); INSERT INTO foo2 VALUES (8); INSERT INTO foo2 VALUES (9); SET enable_seqscan = off; EXPLAIN SELECT * FROM key2 LEFT JOIN foo USING (keyword_id) ; QUERY PLAN - Hash Left Join (cost=20073.50..20191.74 rows=6 width=4) Hash Cond: ("outer".keyword_id = "inner".keyword_id) -> Index Scan using key2_pkey on key2 (cost=0.00..3.03 rows=3 width=4) -> Hash (cost=20062.80..20062.80 rows=4280 width=4) -> Append (cost=1.00..20062.80 rows=4280 width=4) -> Seq Scan on foo (cost=1.00..10031.40 rows=2140 width=4) -> Seq Scan on foo2 foo (cost=1.00..10031.40 rows=2140 width=4) (7 rows) EXPLAIN SELECT * FROM key2 LEFT JOIN (SELECT keyword_id FROM foo WHERE keyword_id IN (SELECT keyword_id FROM key2) ) AS tab USING (keyword_id) ; QUERY PLAN --- Merge Left Join (cost=20087.38..20090.46 rows=3 width=4) Merge Cond: ("outer".keyword_id = "inner".keyword_id) -> Index Scan using key2_pkey on key2 (cost=0.00..3.03 rows=3 width=4) -> Sort (cost=20087.38..20087.39 rows=6 width=4) Sort Key: public.foo.keyword_id -> Hash IN Join (cost=10003.04..20087.30 rows=6 width=4) Hash Cond: ("outer".keyword_id = "inner".keyword_id) -> Append (cost=1.00..20062.80 rows=4280 width=4) -> Seq Scan on foo (cost=1.00..10031.40 rows=2140 width=4) -> Seq Scan on foo2 foo (cost=1.00..10031.40 rows=2140 width=4) -> Hash (cost=3.03..3.03 rows=3 width=4) -> Index Scan using key2_pkey on key2 (cost=0.00..3.03 rows=3 width=4) (12 rows) EXPLAIN SELECT * FROM key2 LEFT JOIN (SELECT keyword_id FROM foo WHERE keyword_id IN (1,6,8) ) AS tab USING (keyword_id) ; QUERY PLAN Merge Left Join (cost=22.08..25.16 rows=3 width=4) Merge Cond: ("outer".keyword_id = "inner".keyword_id) -> Index Scan using key2_pkey on key2 (cost=0.00..3.03 rows=3 width=4) -> Sort (cost=22.08..22.09 rows=6 width=4) Sort Key: public.foo.keyword_id -> Append (cost=3.01..22.00 rows=6 width=4) -> Bitmap Heap Scan on foo (cost=3.01..9.50 rows=3 width=4) Recheck Cond: ((keyword_id = 1) OR (keyword_id = 6) OR (keyword_id = 8)) -> BitmapOr (cost=3.01..3.01 rows=3 width=0) -> Bitmap Index Scan on foo_pkey (cost=0.00..1.00 rows=1 width=0) Index Cond: (keyword_id = 1) -> Bitmap Index Scan on foo_pkey (cost=0.00..1.00 rows=1 width=0) Index Cond: (keyword_id = 6) -> Bitmap Index Scan on foo_pkey (cost=0.00..1.00 rows=1 width=0) Index Cond: (keyword_id = 8) -> Bitmap Heap Scan on foo2 foo (cost=6.01..12.50
Re: [HACKERS] Did this work in earlier version of Postgres?
On Thu, 2006-03-23 at 16:41 -0800, Joshua D. Drake wrote: > Now it gives a error that type double does not exist. > >>> CREATE DOMAIN double AS float8; > >>> > >>> There, now the type exists ;) > >> That's a little too perl for me ;) > > > > I suppose it depends on the goal. If it is an application that is to be > > supported on more than one database, defining types and other things for > > a given DB type (PostgreSQL) is easier than injecting a large number of > > SWITCH statements into the code. > \ > > Why in the world would you build an application for anything except > PostgreSQL? To prove that, as unbelievable as it sounds, it is possible to do such a thing? Don't worry, such a thing would not get into a production environment. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Did this work in earlier version of Postgres?
On Thu, 2006-03-23 at 16:05 -0800, Joshua D. Drake wrote: > Rod Taylor wrote: > > On Thu, 2006-03-23 at 17:31 -0600, Tony Caduto wrote: > >> I could have swore that this worked in earlier releases of Postgresql > >> i.e. 7.4. > >> > >> CREATE TABLE public.test > >> ( > >> junk double NOT NULL, > >> CONSTRAINT junk_pkey PRIMARY KEY (junk) > >> )WITHOUT OIDS; > >> > >> Now it gives a error that type double does not exist. > > > > CREATE DOMAIN double AS float8; > > > > There, now the type exists ;) > > That's a little too perl for me ;) I suppose it depends on the goal. If it is an application that is to be supported on more than one database, defining types and other things for a given DB type (PostgreSQL) is easier than injecting a large number of SWITCH statements into the code. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings