[HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1389)
I updated patch set of SE-PostgreSQL and related stuff (r1389).
[1/5]
http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1389.patch
[2/5]
http://sepgsql.googlecode.com/files/sepostgresql-utils-8.4devel-3-r1389.patch
[3/5]
http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1389.patch
[4/5]
http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1389.patch
[5/5]
http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1389.patch
List of updates:
- The patches are rebased to the latest CVS HEAD, which includes
generic reloptions framework by Alvaro Herrera.
- Row-level ACL's reloptions ("row_level_acl" and "default_row_acl")
are reworked based on the new framework.
Alvaro, could you check the patched code on reloptions.h, reloptions.c
and rel.h? It is a working example of string reloptions, and I could
found a few strange codes.
1. HANDLE_STRING_RELOPTION() always put an empty string when
optstring->default_isnull is true, even if user gives a
valid string reloption.
2. HANDLE_STRING_RELOPTION() cannot handle an offset style.
The patched one enables to put reloption string on the
tail of StdRdOptions structure, and adjust offset value.
3. Why the "StdRdOptions lopts;" is necessary?
A string reloption need to put it on the tail of StdRdOptions
and member of the structure indicates its offset, so it should
be allocated with variable length at the begining.
The patched one invokes palloc0() with sizeof(StdRdOptions)
and length of string at first.
And, I have a request.
4. Is it possible to support a call-back to validate a given
string reloption? I want to check whether the given default
Row-level ACLs has a valid format, or not.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1386)
Tom Lane wrote: > "Robert Haas" writes: >> I haven't looked at the patches, but one thing I'm concerned about is >> the fact that it seems we still don't have a working implementation of >> non-SEPostgresql column-level privileges. Apparently, the latest >> patch set from Stephen Frost doesn't handle those permissions when >> joins are involved, which presumably means that it is not committable. > > No, but it might be fixable. I plan to look at that patch next after > I have my immediate to-do items cleared off ... Stephen, your patch appends attribute numbers on rte->cols_sel list, even if the rte->relkind is RTE_JOIN. Since ExecCheckRTEPerms() skips RangeTblEntry without RTE_RELATION, it is necessary to care special case when the given rte->relkind has RTE_JOIN, isn't it? I think these attribute numbers should be chained on the source RangeTblEntry of the joins to solve the issue. Anyway, I hope your patch getting merged as soon as possible. :-) If I have anything to help you, please feel free to ask for. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
I took your cue, and have formulated this solution for 8.3.1 :
create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;
drop cast (unknown as text);
create cast (unknown as text) with function unknown2text( unknown ) as
implicit;
select '' union all select * from (select '' ) as s;
Thanks for your help Pavel.
Best regards,
PS: I was getting the same error as yours (stack depth) in EDB version
8.3.0.12, so I had to use the following code for unknown2text:
return charin( unknownout($1) );
It works for PG 8.3.1 too.
On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule wrote:
> 2009/1/6 Gurjeet Singh :
> > As I mentioned, we cannot change the query, so adding casts to the query
> is
> > not an option. I was looking for something external to the query, like a
> > CREATE CAST command that'd resolve the issue.
>
> I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
> (but I am have old 8.3)
> postgres=# create function unknown2text(unknown) returns text as
> $$select $1::text$$ language sql;
> CREATE FUNCTION
> postgres=# create cast(unknown as text) with function
> unknown2text(unknown) as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ERROR: stack depth limit exceeded
> HINT: Increase the configuration parameter "max_stack_depth", after
> ensuring the platform's stack depth limit is adequate.
> CONTEXT: SQL function "unknown2text" during startup
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
>
> It working on 8.4
>
> postgres=# create cast (unknown as text) with inout as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ?column?
> --
>
>
> (2 rows)
>
> regards
> Pavel Stehule
>
>
> >
> > Best regards,
>
>
> >
> > On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule
> > wrote:
> >>
> >> Hello
> >>
> >> 2009/1/6 Gurjeet Singh :
> >> > Q1: select '' union all select ''
> >> > Q2: select '' union all select * from (select '' ) as s
> >> >
> >> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
> >> >
> >> > Hi All,
> >> >
> >> > Q1 works just fine, but Q2 fails with:
> >> >
> >> > ERROR: failed to find conversion function from "unknown" to text
> >> >
> >> > Q2 is a generalization of a huge query we are facing, which we
> >> > cannot
> >> > modify. I don't think this is a 'removed-casts' problem generally
> faced
> >> > in
> >> > 8.3, but I may be wrong. Will adding some cast resolve this?
> >>
> >> yes
> >>
> >> postgres=# select '' union all select * from (select ''::text ) as s;
> >> ?column?
> >> --
> >>
> >>
> >> (2 rows)
> >>
> >> regards
> >> Pavel Stehule
> >>
> >> >
> >> > Best regards,
> >> > --
> >> > gurjeet[.sin...@enterprisedb.com
> >> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >> >
> >> > EnterpriseDB http://www.enterprisedb.com
> >> >
> >> > Mail sent from my BlackLaptop device
> >> >
> >
> >
> >
> > --
> > gurjeet[.sin...@enterprisedb.com
> > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh wrote: > I took your cue, and have formulated this solution for 8.3.1 : Is there a good reason you're running against a db version with known bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a version missing over a year of updates is not a best practice. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe wrote:
> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh
> wrote:
> > I took your cue, and have formulated this solution for 8.3.1 :
>
> Is there a good reason you're running against a db version with known
> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a
> version missing over a year of updates is not a best practice.
>
That's just a development instance that I have kept for long; actual issue
was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
8.3.0.12, so had to come up with a different code for that!
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh wrote: > On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe > wrote: >> >> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh >> wrote: >> > I took your cue, and have formulated this solution for 8.3.1 : >> >> Is there a good reason you're running against a db version with known >> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a >> version missing over a year of updates is not a best practice. > > That's just a development instance that I have kept for long; actual issue > was on EDB 8.3.0.12, which the customer is using. As noted in the PS of > previous mail, the solution that worked for PG 8.3.1 didn't work on EDB > 8.3.0.12, so had to come up with a different code for that! Ahh, ok. I was just worried you were ignoring updates. I don't know anything about the numbering scheme for EDB. What does 8.3.0.12 translate to in regular pgsql versions? -- 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] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Stephen R. van den Berg wrote: > > I asked the author of the QuickLZ algorithm about licensing... > Sounds like he is willing to cooperate. This is what I got from him: > > On Sat, Jan 3, 2009 at 17:56, Lasse Reinhold wrote: >> Hi Stephen, >> >> That sounds really exciting, I'd love to see QuickLZ included into >> PostgreSQL. I'd be glad to offer support and add custom optimizations, >> features or hacks or whatever should turn up. >> >> My only concern is to avoid undermining the commercial license, but this >> can, as you suggest, be solved by exceptionally allowing QuickLZ to be >> linked with PostgreSQL. Since I have exclusive copyright of QuickLZ any >> construction is possible. > Another solution could be to make PostgreSQL prepared for using compression with QuickLZ, letting the end user download QuickLZ separately and enable it with a compiler flag during compilation. -- View this message in context: http://www.nabble.com/QuickLZ-compression-algorithm-%28Re%3A-Inclusion-in-the-PostgreSQL-backend-for-toasting-rows%29-tp21284024p21307987.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bugs during ProcessCatchupEvent()
I notice that if an ERROR occurs during ProcessCatchupEvent() then the messages back to client get out of sync with each other. I've inserted an optional error into ProcessCatchupEvent() to show what happens (attached). > postgres=# begin; > BEGIN > postgres=# d; > ERROR: an error occurred while processing catchup event > postgres=# commit; > ERROR: syntax error at or near "d" > LINE 1: commit; > ^ > postgres=# commit; > ROLLBACK > postgres=# begin; > WARNING: there is no transaction in progress > COMMIT Notice how "commit" has been issued twice... and that there is no "d" in commit. LOL, but :-( This issue happens to be exactly the same as the one I have while trying to make SIGINT cancel an idle-in-transaction session. I was looking at the catchup interrupt to try to learn more about this area of code, only to find the same problem exists there also. Perhaps there is no possibility of an ERROR happening during catchup processing, but looking at the rest of ProcessCatchupEvent(), I doubt it. (The attached patch allows behaviour to be turned on/off using synchronous_commit but that has *nothing* to do with this issue and was chosen to avoid inventing a new switch based on what was in miscadmin.h) It looks to me that generating a single error message while idle causes the server to provide ErrorResponse, which the client assumes is the end of the processing of that statement as defined in FE/BE protocol. Yet server continues processing anyway and gives second response later. This also behaves differently on some tests, generating an infinite loop of messages to the log and on the psql client like this: ERROR: an error occurred while processing catchup event message type 0x5a arrived from server while idle ERROR: an error occurred while processing catchup event message type 0x5a arrived from server while idle ... having used over 8 minutes of CPU as I post this, with 1 CPU at 100%, even after the client disconnects. Thoughts, go-look-theres or other comments welcome. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support Index: src/backend/storage/ipc/sinval.c === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/storage/ipc/sinval.c,v retrieving revision 1.89 diff -c -r1.89 sinval.c *** src/backend/storage/ipc/sinval.c 1 Jan 2009 17:23:47 - 1.89 --- src/backend/storage/ipc/sinval.c 5 Jan 2009 19:03:45 - *** *** 303,308 --- 303,311 /* Must prevent SIGUSR2 interrupt while I am running */ notify_enabled = DisableNotifyInterrupt(); + if (!XactSyncCommit) + elog(ERROR, "an error occurred while processing catchup event"); + /* * What we need to do here is cause ReceiveSharedInvalidMessages() to run, * which will do the necessary work and also reset the -- 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] Time to finalize patches for 8.4 beta
On Mon, 2009-01-05 at 17:12 -0800, Josh Berkus wrote: > -- Reducing some DDL Locks to ShareLock (remaining unapplied portions) This just needs some testing by me, so don't kick this one out please. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1389)
KaiGai Kohei wrote: > Alvaro, could you check the patched code on reloptions.h, reloptions.c > and rel.h? It is a working example of string reloptions, and I could > found a few strange codes. I'm intending to revisit the string code ... I was thinking yesterday night that I shouldn't have committed it at all, and left it for a subsequent patch that I had more chance to test properly :-( > 1. HANDLE_STRING_RELOPTION() always put an empty string when >optstring->default_isnull is true, even if user gives a >valid string reloption. This is a plain bug, sorry. > 2. HANDLE_STRING_RELOPTION() cannot handle an offset style. >The patched one enables to put reloption string on the >tail of StdRdOptions structure, and adjust offset value. I'll look at it, thanks. > 3. Why the "StdRdOptions lopts;" is necessary? It is like this because the autovacuum patch adds a few more options and I want to have the chance to not allocate the part belonging to autovacuum when none of the options are present. > And, I have a request. > 4. Is it possible to support a call-back to validate a given >string reloption? I want to check whether the given default >Row-level ACLs has a valid format, or not. Hmm, why a callback and not just call the validation function in heap_reloptions? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
"Gurjeet Singh" writes: > create cast (unknown as text) with function unknown2text( unknown ) as > implicit; This is a horrendously bad idea; it will bite your *ss sooner or later, probably sooner. 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] Time to finalize patches for 8.4 beta
Simon Riggs writes: > On Mon, 2009-01-05 at 17:12 -0800, Josh Berkus wrote: >> -- Reducing some DDL Locks to ShareLock (remaining unapplied portions) > This just needs some testing by me, so don't kick this one out please. The point of the discussion is that we are not going to wait too darn much longer for patches that are "waiting on author". If you want this in 8.4, get that testing finished. Soon. 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] Updates of SE-PostgreSQL 8.4devel patches (r1389)
Alvaro Herrera wrote: KaiGai Kohei wrote: Alvaro, could you check the patched code on reloptions.h, reloptions.c and rel.h? It is a working example of string reloptions, and I could found a few strange codes. I'm intending to revisit the string code ... I was thinking yesterday night that I shouldn't have committed it at all, and left it for a subsequent patch that I had more chance to test properly :-( 1. HANDLE_STRING_RELOPTION() always put an empty string when optstring->default_isnull is true, even if user gives a valid string reloption. This is a plain bug, sorry. 2. HANDLE_STRING_RELOPTION() cannot handle an offset style. The patched one enables to put reloption string on the tail of StdRdOptions structure, and adjust offset value. I'll look at it, thanks. 3. Why the "StdRdOptions lopts;" is necessary? It is like this because the autovacuum patch adds a few more options and I want to have the chance to not allocate the part belonging to autovacuum when none of the options are present. We can return NULL immediately without any allocation, when numoptions=0. Does it give us any pains? http://code.google.com/p/sepgsql/source/browse/trunk/sepgsql/src/backend/access/common/reloptions.c#765 And, I have a request. 4. Is it possible to support a call-back to validate a given string reloption? I want to check whether the given default Row-level ACLs has a valid format, or not. Hmm, why a callback and not just call the validation function in heap_reloptions? I thought you intend to apply validation checks in parse_one_reloption() invoked from parseRelOptions(), but now we have no checks in string reloptions. In my personal preference, it is more simple design parse_one_reloption() invoke a function pointer for validation checks. Please decide a guideline to be followed when we add a new string reloption. If it requires to invoke the function from heap_reloptions(), I'll follow it. Thanks, -- KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Some more function-default issues
Some further reflection about Rushabh Lathia's bug report of yesterday led me to the realization that there's a pretty big hole in the function defaults patch. Since we add default values during planning, it doesn't work for any expression that's not fed through the planner. For instance, ALTER COLUMN USING: regression=# create function add(int, int = 42) returns int regression-# as 'select $1+$2' language sql; CREATE FUNCTION regression=# create table foo(f1 int); CREATE TABLE regression=# insert into foo values (1); INSERT 0 1 regression=# alter table foo alter column f1 type bigint using add(f1)::bigint; ERROR: no value found for parameter 2 CONTEXT: SQL function "add" statement 1 The minimum-code-change solution would be to run around and try to make sure every such expression gets passed through eval_const_expressions() before we try to execute it. This is probably doable (looking for calls to fix_opfuncids would be a good guide) but it seems like the potential for errors of omission is large, particularly in third-party add-ons. I wonder if anyone has an idea for a better way to attack this? 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: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane wrote:
> "Gurjeet Singh" writes:
> > create cast (unknown as text) with function unknown2text( unknown ) as
> > implicit;
>
> This is a horrendously bad idea; it will bite your *ss sooner or later,
> probably sooner.
>
>regards, tom lane
>
I guessed so, but couldn't figure out exactly how! That's why I have
suggested this as a temp solution until we confirmed this with someone more
knowledgeable.
Can you please let us know how this would be problematic? And can you
suggest a better solution?
Thanks and best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [HACKERS] [PATCH] ALTER TABLE SET (compress_max_size... = )
Alex Hunsaker wrote: > This patch lets you control 3 pg_lzcompress knobs on a per table basis > (note requires reloptions.patch) > > compress_max_size: Controls the maximum size to be considered for > TOAST compression. > compress_min_rate: Minimum compression rate (0-100%) required for > TOAST compression to be used. > compress_success_by: if by this byte no compressible data found abort > compression. > > Note this adds some documentation, but I was having a hard time coming > up with a good way to describe these. I'm also not very happy with > the names. I originally tried something like toast.max_input_size. > But decided later if we allow you to set toast attributes that might > be confusing. So help with verbiage and names is appreciated. > > Also I only did those 3 because they seemed the 3 most useful things > someone would want to tune. Later if we need to we can export them > all and make them per column settings (and maybe you can pick a > compression algo or what not...) But I figured lets start small. > > I thought about doing another cleanup patch to get rid of > PGLZ_Strategy_default and PGLZ_Strategy_always. Nothing uses the > later, and if we expose all the nobs nothing will use the first. > Comments? I think we need to live with the TOAST changes for at least one release before we know what knobs we will need. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
"Gurjeet Singh" writes: >> This is a horrendously bad idea; it will bite your *ss sooner or later, >> probably sooner. > Can you please let us know how this would be problematic? The point is that it's going to have unknown, untested effects on the default coercion rules, possibly leading to silent changes in the behavior of queries that used to work. If you'd rather retest every one of your other queries than fix this one, then go ahead. 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] lazy_truncate_heap()
Simon Riggs wrote: On Wed, 2008-12-31 at 21:45 +0200, Heikki Linnakangas wrote: Can I fix? Yes please. Fix attached. --- 183,192 * number of pages. Otherwise, the time taken isn't worth it. */ possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; ! if (vacrelstats->tuples_deleted > 0 && ! (possibly_freeable >= REL_TRUNCATE_MINIMUM || !(possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION && ! possibly_freeable > 0))) lazy_truncate_heap(onerel, vacrelstats); Where did that "tuples_deleted > 0" condition come from? It seems counter-productive; if a previous vacuum failed to acquire the lock, subsequent vacuums wouldn't even try if they don't remove any tuples. How about simply: *** *** 183,190 * number of pages. Otherwise, the time taken isn't worth it. */ possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; ! if (possibly_freeable >= REL_TRUNCATE_MINIMUM || ! possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION) lazy_truncate_heap(onerel, vacrelstats); /* Vacuum the Free Space Map */ --- 183,191 * number of pages. Otherwise, the time taken isn't worth it. */ possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; ! if (possibly_freeable > 0 && ! (possibly_freeable >= REL_TRUNCATE_MINIMUM || !possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION)) lazy_truncate_heap(onerel, vacrelstats); /* Vacuum the Free Space Map */ -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lazy_truncate_heap()
On Tue, 2009-01-06 at 15:48 +0200, Heikki Linnakangas wrote: > How about simply: OK -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
--On Montag, Januar 05, 2009 17:12:27 -0800 Josh Berkus wrote: -- Automatic View Update Rules I'm currently adressing the issues Jaime mentioned and will provide an updated patch very soon. -- Thanks Bernd -- 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] Some more function-default issues
Tom Lane wrote: The minimum-code-change solution would be to run around and try to make sure every such expression gets passed through eval_const_expressions() before we try to execute it. This is probably doable (looking for calls to fix_opfuncids would be a good guide) but it seems like the potential for errors of omission is large, particularly in third-party add-ons. That seems ok to me. Calling eval_const_expressions() in ALTER COLUMN and elsewhere is a good idea for performance reasons as well. I can only find one more call to fix_opfuncids, where we're not already calling eval_const_expressions(): GetDomainConstraints(). Adding a eval_const_expressions() call to ExecPrepareExpr() would take care of the ALTER COLUMN and many other cases where we have a problem now. I can't imagine a third-party add-on so tightly integrated with the backend that it needs to mess with Expr nodes, and call fix_opfuncids(). ExecPrepareExpr, maybe, but if we fix that as I presume we would, the add-ons wouldn't be affected. Overall, I don't see much potential for bugs-of-omission. You could put a comment at the top of fix_opfuncids() as a reminder that outside the executor you need to call eval_const_expressions() too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs during ProcessCatchupEvent()
Simon Riggs writes: > It looks to me that generating a single error message while idle causes > the server to provide ErrorResponse, which the client assumes is the end > of the processing of that statement as defined in FE/BE protocol. Yeah. I think this is actually a client-side issue: it should keep reading till it gets a 'Z' message. Not clear how that fits into the libpq-to-app API though. 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] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
>>> not compressing very small datums (< 256 bytes) also seems smart, >>> since that could end up producing a lot of extra compression attempts, >>> most of which will end up saving little or no space. > > That was presumably the rationale for the original logic. However experience > shows that there are certainly databases that store a lot of compressible > short strings. > > Obviously databases with CHAR(n) desperately need us to compress them. But > even plain text data are often moderately compressible even with our fairly > weak compression algorithm. > > One other thing that bothers me about our toast mechanism is that it only > kicks in for tuples that are "too large". It seems weird that the same column > is worth compressing or not depending on what other columns are in the same > tuple. That's a fair point. There's definitely some inconsistency in the current behavior. It seems to me that, in theory, compression and out-of-line storage are two separate behaviors. Out-of-line storage is pretty much a requirement for dealing with large objects, given that the page size is a constant; compression is not a requirement, but definitely beneficial under some circumstances, particularly when it removes the need for out-of-line storage. char(n) is kind of a wierd case because you could also compress by storing a count of the trailing spaces, without applying a general-purpose compression algorithm. But either way the field is no longer fixed-width, and therefore field access can't be done as a simple byte offset from the start of the tuple. It's difficult even to enumerate the possible use cases, let alone what knobs would be needed to cater to all of them. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some more function-default issues
Heikki Linnakangas writes: > That seems ok to me. Calling eval_const_expressions() in ALTER COLUMN > and elsewhere is a good idea for performance reasons as well. Yeah, probably so. > I can only find one more call to fix_opfuncids, where we're not already > calling eval_const_expressions(): GetDomainConstraints(). Adding a > eval_const_expressions() call to ExecPrepareExpr() would take care of > the ALTER COLUMN and many other cases where we have a problem now. I'd prefer not to have ExecPrepareExpr do it, though; that's supposed to be working from a read-only expression tree supplied by the caller. (The fix_opfuncids call in it is already pushing the bounds of that concept.) >From a structural point of view the right thing would be to introduce a concept of "expression planning", along the lines of expr = plan_expression(expr); which callers would be required to invoke before ExecPrepareExpr. Right now this would do the fix_opfuncids bit and eval_const_expressions, but I could see someday allowing SubLinks in standalone expressions because we'd have the ability to invoke the full planner from inside here. The trick is to get the attention of third-party code about the need to make this change. Removing fix_opfuncids from ExecPrepareExpr wouldn't really help much, because in very many common cases it's a no-op anyway; so unless their testing is quite thorough they would not see a failure before shipping. The only idea I have at the moment is to rename ExecPrepareExpr to something else, but it's not clear if that will persuade people to read its header comment or not ... 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
[HACKERS] Hot Standby Query Conflicts
After various discussions over last few months, it's now time to finalise the way we handle query conflicts in Hot Standby. Please can interested people read http://wiki.postgresql.org/wiki/Hot_Standby#Query_Conflicts which is user docs for how HS will handle this. Remember we're nearly in beta, so this is really just to check understanding rather than to generate further ideas and debate. The whole of http://wiki.postgresql.org/wiki/Hot_Standby#Usage is also worth reading, which now runs to about 4-5 pages/screens. Design overview stuff is somewhat historical now, but may be interesting to read since it continues earlier design thoughts. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs during ProcessCatchupEvent()
On Tue, 2009-01-06 at 09:44 -0500, Tom Lane wrote: > Simon Riggs writes: > > It looks to me that generating a single error message while idle causes > > the server to provide ErrorResponse, which the client assumes is the end > > of the processing of that statement as defined in FE/BE protocol. > > Yeah. I think this is actually a client-side issue: it should keep > reading till it gets a 'Z' message. Not clear how that fits into the > libpq-to-app API though. That makes sense. I'll dig around there. The infinite loop error seems server-side though. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas escribió: > char(n) is kind of a wierd case because you could also compress by > storing a count of the trailing spaces, without applying a > general-purpose compression algorithm. But either way the field is no > longer fixed-width, and therefore field access can't be done as a > simple byte offset from the start of the tuple. That's not the case anyway (fixed byte width) due to possible multibyte chars. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] lazy_truncate_heap()
Simon Riggs wrote: On Tue, 2009-01-06 at 15:48 +0200, Heikki Linnakangas wrote: How about simply: OK Committed and backpatched all the way back to 7.4 stable. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Warning about the 8.4 release
I am now warning that we have an unusually large number of open items that must be either completed or moved to the TODO list before 8.4 can be released. Everyone knows about the commit fest wiki items, but I am tracking 291 threads that need some type of attention; if only 50% of them are significant for 8.4, that still leaves +100 items that should be completed in the next month. You can see the full list here: http://momjian.us/cgi-bin/pgpatches If we just move them all to the TODO list, the TODO list will be significantly larger for 8.4 than in previous releases, and fixing items only gets harder as we get farther away from the email discussion that prompted the item (and we risk a MySQL 5.1 debacle). I have already approached developers to get help in completing these items, but got little assistance. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
Bruce Momjian writes: > Everyone knows about the commit fest wiki items, but I am tracking 291 > threads that need some type of attention; if only 50% of them are > significant for 8.4, that still leaves +100 items that should be > completed in the next month. You can see the full list here: > http://momjian.us/cgi-bin/pgpatches > If we just move them all to the TODO list, the TODO list will be > significantly larger for 8.4 than in previous releases, ... and will be mostly junk. 90% of what you've got there is either already dealt with or not a bug, and certainly not new bugs in 8.4. 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] Warning about the 8.4 release
Tom Lane wrote: > Bruce Momjian writes: > > Everyone knows about the commit fest wiki items, but I am tracking 291 > > threads that need some type of attention; if only 50% of them are > > significant for 8.4, that still leaves +100 items that should be > > completed in the next month. You can see the full list here: > > > http://momjian.us/cgi-bin/pgpatches > > > If we just move them all to the TODO list, the TODO list will be > > significantly larger for 8.4 than in previous releases, > > ... and will be mostly junk. 90% of what you've got there is either > already dealt with or not a bug, and certainly not new bugs in 8.4. You are right that 90% are either not new or not bugs, but they are things that need correction, and no one benefits by just pushing them off to a later release. As I said, if even 50% are things that need work, they should be completed for 8.4 rather than added to the TODO list. These are all things that have been reported during the 8.4 release cycle. Are you saying we no longer care about fixing small issues? I think the project quality will suffer. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
Bruce Momjian wrote: > Everyone knows about the commit fest wiki items, but I am tracking 291 > threads that need some type of attention; if only 50% of them are > significant for 8.4, that still leaves +100 items that should be > completed in the next month. You can see the full list here: > > http://momjian.us/cgi-bin/pgpatches I notice that some of the items in this commitfest are in your queue. Some things are duplicated; for example parallel restore appear more than once (I count six times). Column privileges is also several times. Also some patches have already been applied; for example: Item 14, "[HACKERS] Another refactoring proposal: move stuff into nodes/nodeFuncs.[ch]" was applied. Item 57, "Re: [HACKERS] autovacuum and TOAST tables" was applied. Item 78, bug 4495 is not a memory leak Item 79, bug 4496 is not a leak either Item 86 was rejected Item 103 (plpython) is a commitfest item that was returned to author I'm not sure why is item 106 (heap_formtuple) listed? Item 118 (pgdump roles, Ibrar Ahmed) is applied Item 130 (PD_PAGE_FULL) is bogus; drop it I'm not sure why you have planetpostgresql.org items? Item 155 (toast by chunk end) can be dropped from this list; it's an unneeded patch. Item 162 is an open item??? "cool hacks with recursive queries" Item 166, "designated initializers" can be dropped (part of reloptions patch that's already committed) Item 184 "working in the US" is a pgsql-core email, not even in the archives ... Item 197, "links to cvsweb from archive" can be dropped; problem was fixed. [checks] Uh, actually it is broken again. I guess Marc upgraded the web server. Item 242, "generic reloptions improvement", is applied Item 272, "protein database", is here because ...? Item 286 (32/64 bits) was already fixed by yourself? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
I realize the list is incomplete and inaccurate. My point is if only 50% need work, we still have lots of work to do. I will update my mailbox and post a new version soon. --- Alvaro Herrera wrote: > Bruce Momjian wrote: > > > Everyone knows about the commit fest wiki items, but I am tracking 291 > > threads that need some type of attention; if only 50% of them are > > significant for 8.4, that still leaves +100 items that should be > > completed in the next month. You can see the full list here: > > > > http://momjian.us/cgi-bin/pgpatches > > I notice that some of the items in this commitfest are in your queue. > Some things are duplicated; for example parallel restore appear more > than once (I count six times). Column privileges is also several > times. > > Also some patches have already been applied; for example: > > Item 14, "[HACKERS] Another refactoring proposal: move stuff into > nodes/nodeFuncs.[ch]" was applied. > > Item 57, "Re: [HACKERS] autovacuum and TOAST tables" was applied. > > Item 78, bug 4495 is not a memory leak > > Item 79, bug 4496 is not a leak either > > Item 86 was rejected > > Item 103 (plpython) is a commitfest item that was returned to author > > I'm not sure why is item 106 (heap_formtuple) listed? > > Item 118 (pgdump roles, Ibrar Ahmed) is applied > > Item 130 (PD_PAGE_FULL) is bogus; drop it > > I'm not sure why you have planetpostgresql.org items? > > Item 155 (toast by chunk end) can be dropped from this list; it's an unneeded > patch. > > Item 162 is an open item??? "cool hacks with recursive queries" > > Item 166, "designated initializers" can be dropped (part of reloptions > patch that's already committed) > > Item 184 "working in the US" is a pgsql-core email, not even in the > archives ... > > Item 197, "links to cvsweb from archive" can be dropped; problem was > fixed. [checks] Uh, actually it is broken again. I guess Marc upgraded > the web server. > > Item 242, "generic reloptions improvement", is applied > > Item 272, "protein database", is here because ...? > > Item 286 (32/64 bits) was already fixed by yourself? > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
On Tue, Jan 6, 2009 at 3:21 PM, Bruce Momjian wrote: > I am now warning that we have an unusually large number of open items > that must be either completed or moved to the TODO list before 8.4 can > be released. > > Everyone knows about the commit fest wiki items, but I am tracking 291 > threads that need some type of attention; if only 50% of them are > significant for 8.4, that still leaves +100 items that should be > completed in the next month. You can see the full list here: 243 seems like a priority for release :-p 253 was a website issue that's been fixed. 158 was fixed this morning by pure chance. 170 is a private message on -core And the one bug (which I can't find now) that I've logged recently is missing... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
Dave Page wrote: > On Tue, Jan 6, 2009 at 3:21 PM, Bruce Momjian wrote: > > I am now warning that we have an unusually large number of open items > > that must be either completed or moved to the TODO list before 8.4 can > > be released. > > > > Everyone knows about the commit fest wiki items, but I am tracking 291 > > threads that need some type of attention; if only 50% of them are > > significant for 8.4, that still leaves +100 items that should be > > completed in the next month. You can see the full list here: > > 243 seems like a priority for release :-p > 253 was a website issue that's been fixed. > 158 was fixed this morning by pure chance. Can you give subject lines on this? I didn't mean for people to actually start working on items, but rather just to see the scope of the problem. I am making updates based on Alvaro's comments now. > 170 is a private message on -core OK removed. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
On Tue, 2009-01-06 at 10:21 -0500, Bruce Momjian wrote: > I have already approached developers to get help in completing these > items, but got little assistance. If you can send me the list that you think applies to me, I'll work on it. I don't want to spend the time to read every entry if you already have. Thanks. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
Alvaro Herrera wrote: > Bruce Momjian wrote: > > > Everyone knows about the commit fest wiki items, but I am tracking 291 > > threads that need some type of attention; if only 50% of them are > > significant for 8.4, that still leaves +100 items that should be > > completed in the next month. You can see the full list here: > > > > http://momjian.us/cgi-bin/pgpatches > > I notice that some of the items in this commitfest are in your queue. > Some things are duplicated; for example parallel restore appear more > than once (I count six times). Column privileges is also several > times. > > Also some patches have already been applied; for example: > > Item 14, "[HACKERS] Another refactoring proposal: move stuff into > nodes/nodeFuncs.[ch]" was applied. OK, removed. > Item 57, "Re: [HACKERS] autovacuum and TOAST tables" was applied. Removed. > Item 78, bug 4495 is not a memory leak > > Item 79, bug 4496 is not a leak either > > Item 86 was rejected OK, on above. > Item 103 (plpython) is a commitfest item that was returned to author OK, I will grab that from the commit wiki and add to the TODO list later. Below all removed: > I'm not sure why is item 106 (heap_formtuple) listed? > > Item 118 (pgdump roles, Ibrar Ahmed) is applied > > Item 130 (PD_PAGE_FULL) is bogus; drop it > > I'm not sure why you have planetpostgresql.org items? > > Item 155 (toast by chunk end) can be dropped from this list; it's an unneeded > patch. > > Item 162 is an open item??? "cool hacks with recursive queries" > > Item 166, "designated initializers" can be dropped (part of reloptions > patch that's already committed) > > Item 184 "working in the US" is a pgsql-core email, not even in the > archives ... > > Item 197, "links to cvsweb from archive" can be dropped; problem was > fixed. [checks] Uh, actually it is broken again. I guess Marc upgraded > the web server. > > Item 242, "generic reloptions improvement", is applied > > Item 272, "protein database", is here because ...? > Item 286 (32/64 bits) was already fixed by yourself? This needs Magnus to do the MSVC build. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
Dave Page wrote: > On Tue, Jan 6, 2009 at 4:12 PM, Bruce Momjian wrote: > > Dave Page wrote: > > >> 243 seems like a priority for release :-p > > [GENERAL] happy holidays, christmas etc., Removed. > >> 253 was a website issue that's been fixed. > > [pgsql-www] Re: [pgsql-advocacy] Problem with "File Browser" link on > downloadspage Great. > >> 158 was fixed this morning by pure chance. > > Re: [BUGS] BUG #4538: shared memory Good. > > Can you give subject lines on this? > > Yikes, I didn't spot that the numbers could change. Seems to make them > kinda useless. Yep. > > I didn't mean for people to > > actually start working on items, but rather just to see the scope of the > > problem. > > Well, I don't think we are seeing the scope of the problem, given that > we've had 20+ false non-problems spotted within a few minutes of your > list being posted. Once it's cleaned up we'll see the scope of the > problem (not that I believe we have one - I think we have what is > shaping up to be a superb release, if a touch later than originally > planned). > > BTW, the missing bug is > http://archives.postgresql.org/pgsql-bugs/2008-11/msg00106.php I have added that to the 8.4 open items wiki. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
On Tue, Jan 6, 2009 at 4:12 PM, Bruce Momjian wrote: > Dave Page wrote: >> 243 seems like a priority for release :-p [GENERAL] happy holidays, christmas etc., >> 253 was a website issue that's been fixed. [pgsql-www] Re: [pgsql-advocacy] Problem with "File Browser" link on downloadspage >> 158 was fixed this morning by pure chance. Re: [BUGS] BUG #4538: shared memory > Can you give subject lines on this? Yikes, I didn't spot that the numbers could change. Seems to make them kinda useless. > I didn't mean for people to > actually start working on items, but rather just to see the scope of the > problem. Well, I don't think we are seeing the scope of the problem, given that we've had 20+ false non-problems spotted within a few minutes of your list being posted. Once it's cleaned up we'll see the scope of the problem (not that I believe we have one - I think we have what is shaping up to be a superb release, if a touch later than originally planned). BTW, the missing bug is http://archives.postgresql.org/pgsql-bugs/2008-11/msg00106.php -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] incoherent view of serializable transactions
>>> Paul Schlie wrote: > Sorry if I'm restating the obvious, however I don't understand the > confusion, as it seems the standard's definition isn't mysterious; > it simply requires that the resulting state from the concurrent > execution of transactions (and implicitly any subset) designated to > occur at the isolation level SERIALIZABLE be equivalent to SOME > LITERALLY SERIAL execution of said transactions. I think that some of the confusion may result from changes in the standard. As far as I can recall, the language requiring that the SERIALIZABLE transaction isolation level be truly serializable was not in early versions of the standard, and it may be that there is some reluctance to concede that a shift in the standard has rendered PostgreSQL out of compliance on this point. As I see it, the discussion on this thread is around recognition of the requirements of the current standard within the PostgreSQL documentation. There is a related thread on which I'm attempting to come up with documentation to assist those familiar with true serializable behavior who are attempting to recognize application coding patterns where the differences between that and snapshot isolation are material, with tips on how to handle these differences. There seems to be some question whether the patterns in which anomalies occur are common enough to merit comment. If you could reference any concise and accessible work on these anomalies and practical workarounds in application code, it would be much appreciated. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4186: set lc_messages does not work
Hi. Sorry very late reaction I report the test checked again. http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES_01.png http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES_02.png http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES_03.png http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES_04.png and http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES.sql Then, set PGCLIENTENCODING=SJIS http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/LC_MESSAGES.log However, libintl needs to be created correctly.(Inoue-san prepares this.) http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/bin/ http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/include/ http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/lib/ in CVS-HEAD of the newest patch, line positions differ for a while.(.Inoue-san prepares this.) http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/mbutils_2.patch http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/libintl_check/win_lc_messages_2.patch Conclusion, looks at a good result.!! Regards, Hiroshi Saito - Original Message - From: "Hiroshi Inoue" Oops, I forgot to attach the patch, sorry. Hiroshi Inoue wrote: Hi, I posted a patch 18 days ago but have got no responce. Anyway I've simplified the patch by using an appropriate gettext module. Hiroshi Inoue wrote: Bruce Momjian wrote: Tom Lane wrote: Magnus Hagander writes: Thomas H. wrote: so at least that explains the "changed" behaviour. nevertheless, LC_MESSAGES seems to be defunct - with the "locale" folder present, pg always picks the os' language and ignores the lc_message value. This looks like I can reproduce though, at least on cvs head. Did this work for you in previous versions? Maybe we were using a different build of gettext in the previous releases, one that didn't look at the same info as the current code? Where are we on this? AFAICS there are 2 causes. 1. MSVC version of postgres is using a bad gettext module. 2. getenv() in mingw cannot see the result of putenv() in MSVC8.0. As for 1, we have to use another gettext module. I can provide it if requested. As for 2, pg_putenv() or pg_unsetenv() in the attachced patch calls corresponding putenv() whose result can be referenced by getenv() in mingw. In addtion the patch provides a functionality to Windows locale name to ISO formatted locale name. Comments ? regards, Hiroshi Inoue -- 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] Warning about the 8.4 release
> Can you give subject lines on this? I didn't mean for people to > actually start working on items, but rather just to see the scope of the > problem. Hmm, well, when you are ready for people to start working on items, I might be able to work on some items, if there are things that a non-core, non-committer community member such as myself can help with. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
Bruce Momjian wrote: > I am now warning that we have an unusually large number of open items > that must be either completed or moved to the TODO list before 8.4 can > be released. > > Everyone knows about the commit fest wiki items, but I am tracking 291 > threads that need some type of attention; if only 50% of them are > significant for 8.4, that still leaves +100 items that should be > completed in the next month. You can see the full list here: OK, based on excellent feedback I have updated the list of open issues (down from 291 to 261): http://momjian.us/cgi-bin/pgpatches If people have further updates please, please send them (with subject headings please). ;-) I am excited people looked at the list and were able to tell me some of them are closed. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
Robert Haas wrote: > > Can you give subject lines on this? I didn't mean for people to > > actually start working on items, but rather just to see the scope of the > > problem. > > Hmm, well, when you are ready for people to start working on items, I > might be able to work on some items, if there are things that a > non-core, non-committer community member such as myself can help with. Yep, many are just documentation updates or things where you can send in patches. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Documenting serializable vs snapshot isolation levels
I've rearranged the sequence of some lines in the previous post to facilitate discussion. I hope no offense is taken. >>> "Robert Haas" wrote: > On further review, I actually think that our documentation is pretty > clear about this topic, too. Everything we've talked about thus far > all seems to be spelled out in chapter 13: > > http://www.postgresql.org/docs/8.3/interactive/mvcc-intro.html > http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html > http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html > http://www.postgresql.org/docs/8.3/interactive/applevel-consistency.html > > Note in particular section 13.2.2.1. Serializable Isolation versus > True Serializability I read all of the above over very carefully, several times, before starting this thread. These are precisely the sections I feel could use correction and improvement. > Doing it this way rather than using a foreign key constraint > is dumb, and a foreign key constraint works fine The point is that it is something that would work reliably under serializable isolation, but not under snapshot isolation. I picked it merely because it is a simple integrity test that someone might choose to enforce in a trigger in some other database, and might not recognize it as an unreliable technique in PostgreSQL. Dumb or not, they may lose integrity after moving to PostgreSQL if they miss this, and I propose documenting the issue to assist people. > The only problems > you've raised so far are well-known problems in database theory; I > learned about them from Jim Gray's 1993 "Transaction Processing", but > that's about a 700 page book. I suspect there are shorter texts that > you could read to pick up the main ideas but I'm not familiar with > them so I can't provide any pointers. > With respect to your example here, we're right back to what I said way > upthread: if you're worried about concurrent updates or deletes, > SELECT ... FOR SHARE is sufficient. If you're worried about > concurrent inserts, as you are here (delete from parent wants to make > sure no row can be concurrently inserted into child), you need to take > a SHARE lock on the table into which you want to prevent inserts. This advice seems consistent with the current PostgreSQL documentation (cited above) and might lead one to believe that in the example you reference, adding a FOR SHARE to the SELECT which confirms the existence of the parent row, and a LOCK TABLE on the child table at the start of the transaction which does the DELETE of the parent would provide integrity. It does not; try it if you want confirmation. It does introduce blocking, but after the block clears, the result in the database is identical to the example as originally posted. This is why I think the documentation could use enhancement. > It really seems to me that we're going around in circles here. Agreed. I guess I contributed to that by questioning whether "most" or "many" was a more appropriate adjective, which is pretty irrelevant, really. I'll try to stay focused on examples of things that work in one environment and don't in the other, with tips to get the desired behavior within PostgreSQL. I have come up with many more examples of these than I have posted on-list, but posting every single example doesn't seem valuable to me. I'm trying to generalize to provide useful guidelines, but feel sure that I'm re-inventing the wheel here. Thanks for suggesting Jim Gray's "Transaction Processing". I'll look for it. If it's framing things from a theoretical point of view, there will be some work necessary to distill it down to the concise and practical advice which I've found necessary to effectively guide application programmers, but at least I can do it with more confidence that I've covered all the relevant ground. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore --clean text
Erik Rijkers wrote:
> The help text for the pg_restore --clean option in the documentation is IMHO
> more precise than the
> text that the pg_restore binary gives:
>
> documentation:
> -c
> --clean
>Clean (drop) database objects before recreating them.
>
> pg_restore binary:
>-c, --clean clean (drop) schema prior to create
>
>
> So I think it would be an improvement to channge the pg_restore --help text:
I used the wording from the pg_restore SGML manual page in the --help
text, to be more consistent. Thanks for the report.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_restore.c
===
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_restore.c,v
retrieving revision 1.90
diff -c -c -r1.90 pg_restore.c
*** src/bin/pg_dump/pg_restore.c 5 Jan 2009 16:54:37 - 1.90
--- src/bin/pg_dump/pg_restore.c 6 Jan 2009 17:17:10 -
***
*** 391,397
printf(_("\nOptions controlling the restore:\n"));
printf(_(" -a, --data-only restore only the data, no schema\n"));
! printf(_(" -c, --clean clean (drop) schema prior to create\n"));
printf(_(" -C, --create create the target database\n"));
printf(_(" -I, --index=NAME restore named index\n"));
printf(_(" -L, --use-list=FILENAME use specified table of contents for ordering\n"
--- 391,397
printf(_("\nOptions controlling the restore:\n"));
printf(_(" -a, --data-only restore only the data, no schema\n"));
! printf(_(" -c, --clean clean (drop) database objects before recreating\n"));
printf(_(" -C, --create create the target database\n"));
printf(_(" -I, --index=NAME restore named index\n"));
printf(_(" -L, --use-list=FILENAME use specified table of contents for ordering\n"
--
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] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Is there any progress on this patch? I was asked about this feature last month, during a PostgreSQL talk. I am willing to spend time for testing this patch, if needed. -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCH] ALTER TABLE SET (compress_max_size... = )
On Tue, Jan 6, 2009 at 06:43, Bruce Momjian wrote: > Alex Hunsaker wrote: >> This patch lets you control 3 pg_lzcompress knobs on a per table basis >> (note requires reloptions.patch) > > I think we need to live with the TOAST changes for at least one release > before we know what knobs we will need. Fine with me. The add an early failure path and increase required compression rate to 25% still worry me a bit. But I have no data to show the first one is actually a problem. And the second one only caused a 15% size increase for me. If that's a typical size increase or problem, i dunno -- 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] Warning about the 8.4 release
On Tue, 2009-01-06 at 11:30 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > > > On Tue, 2009-01-06 at 10:21 -0500, Bruce Momjian wrote: > > > > > I have already approached developers to get help in completing these > > > items, but got little assistance. > > > > If you can send me the list that you think applies to me, I'll work on > > it. I don't want to spend the time to read every entry if you already > > have. Thanks. > > Sure, I think all your stuff is on the commit fest page so it is already > being dealt with. I've looked at 65, 65, 67, 69, 104, 109, 112, 145, 163, 175, 178, 225, 226, 233, 276 and 285 to extract any additional points not already noted on the Hot Standby wiki. Nothing burning, just couple of minor issues picked up. 112 is a duplicate of 178. Both are unrelated directly to HS, though it needs attention for 8.4. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
It seems like it would be helpful if you made a pass through it yourself just looking for duplicates and commitfest items, since it's probably just as fast for you to find them and fix them as it is for us to tell you about them - maybe faster, since the links on this page don't seem to work very well. But... - you have column-level privileges in there four times, it's also in the commitfest - updatable views is in the commitfest, and is the same item as WIP: Automatic view update rules - WIP: Hash Join-Filter Pruning using Bloom Filters is in the commitfest - SQL/MED compatible connection manager is in the commitfest and in fact comitted - optimizing copy is on the commitfest page (under "optimizing copy with memchr") - htup and bufpage API clean up is on the commitfest page - HeapTuple version extension + code cleanup is on the commitfest page - Synchronous replication patch v1 and synchronous replication patch v2 are earlier versions of the patch that is now one of the major items left to resolve for this commitfest - updated hash functions for postgresql v1 is in on the commitfest page - WIP: Page space reservation (pgupgrade) is an idea that was rejected, IIRC. pg_upgrade project status is more of the same thing. there are several more pg_upgrade related items on here as well, most of which are probably unnecessary. - Infrastructure changes for recovery (v8) is on the commitfest page - parallel restore is on here several times under various times: parallel pg_restore - WIP patch, parallel pg_restore, parallel pg_restore design issues. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore --clean text
Bruce Momjian writes: > I used the wording from the pg_restore SGML manual page in the --help > text, to be more consistent. Thanks for the report. pg_dump has the same wording. pg_dumpall might need adjustment too, though I'm not sure (note its --clean acts on DBs not individual objects) 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: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane wrote:
> "Gurjeet Singh" writes:
> >> This is a horrendously bad idea; it will bite your *ss sooner or later,
> >> probably sooner.
>
> > Can you please let us know how this would be problematic?
>
> The point is that it's going to have unknown, untested effects on the
> default coercion rules, possibly leading to silent changes in the
> behavior of queries that used to work. If you'd rather retest every one
> of your other queries than fix this one, then go ahead.
>
>
Changing the query is an option not given to us. It is being migrated from a
BigDB.
I was working on these solutions assuming that these are workarounds to a
bug. But from your mails, it seems that it is an expected behaviour; is it?
If we consider the second branch of UNION ALL of both the queries above, if
"select '' " yields a text column, then so should a "select * from (select
'')".
Its not exactly a bug, but sure is a problem that we should try to resolve.
Thanks and best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [HACKERS] dblink vs SQL/MED - security and implementation details
On Tuesday 06 January 2009 05:54:14 Joe Conway wrote:
> --
> -- now as untrusted user dblink_regression_test
> --
> contrib_regression=> SELECT dblink_connect('myconn', 'fdtest');
> dblink_connect
>
> OK
> (1 row)
I think you want some permission checking on fdtest then, right?
--
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] dblink vs SQL/MED - security and implementation details
Peter Eisentraut writes: > I think you want some permission checking on fdtest then, right? What about the permissions on the system catalogs themselves? AFAICT, the pg_user_mappings view will expose user passwords to the "owner" of the foreign server, which doesn't seem good. 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
[HACKERS] SQL/MED dummy vs postgresql wrapper
I have been thinking that we are setting up the foreign-data wrapper dummies
wrongly.
Eventually, the postgresql_fdw library should contain an implementation that
actually connects to a PostgreSQL database and does useful things (dblink
replacement, basically). Right now, we are proposing to use it as connection
information storage. But I think that might get us in trouble later.
Loading a fully implemented postgresql_fdw might do significant work, which
you don't really want when you are just querying the connection parameters.
(This is not completely theoretical: Firing up libpq might do zeroconf
queries or in the far future even connection pooling.) We have conflicting
use cases there: We are loading up a library that we don't intend to use.
I think the proper approach is to separate these concerns: Have one FDW
implementation that (eventually) does real PostgreSQL connectivity, and one
that just does parameter storage. We could name the latter postgresql_dummy,
but I also have another idea: We could just use the dummy wrapper and set an
option for the foreign data wrapper that tells what options are valid. That
is, you would say
CREATE FOREIGN DATA WRAPPER postgresql_dummy LIBRARY 'dummy_fdw' LANGUAGE C
OPTIONS (valid_options '{host,port,dbname,user,password...}');
CREATE SERVER server1 FOREIGN DATA WRAPPER postgresql_dummy OPTIONS
(host 'localhost');
CREATE USER MAPPING FOR current_user SERVER server1 OPTIONS
(password 'seKret');
That way, you would have more flexibility, less code, and less potential
conflicts in the future.
Comments?
--
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_restore --clean text
Tom Lane wrote:
> Bruce Momjian writes:
> > I used the wording from the pg_restore SGML manual page in the --help
> > text, to be more consistent. Thanks for the report.
>
> pg_dump has the same wording. pg_dumpall might need adjustment too,
> though I'm not sure (note its --clean acts on DBs not individual
> objects)
OK, done with attached patch.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.512
diff -c -c -r1.512 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 5 Jan 2009 16:54:37 - 1.512
--- src/bin/pg_dump/pg_dump.c 6 Jan 2009 18:01:06 -
***
*** 803,809
printf(_("\nOptions controlling the output content:\n"));
printf(_(" -a, --data-only dump only the data, not the schema\n"));
printf(_(" -b, --blobs include large objects in dump\n"));
! printf(_(" -c, --clean clean (drop) schema prior to create\n"));
printf(_(" -C, --createinclude commands to create database in dump\n"));
printf(_(" -d, --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" -D, --column-insertsdump data as INSERT commands with column names\n"));
--- 803,809
printf(_("\nOptions controlling the output content:\n"));
printf(_(" -a, --data-only dump only the data, not the schema\n"));
printf(_(" -b, --blobs include large objects in dump\n"));
! printf(_(" -c, --clean clean (drop) database objects before recreating\n"));
printf(_(" -C, --createinclude commands to create database in dump\n"));
printf(_(" -d, --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" -D, --column-insertsdump data as INSERT commands with column names\n"));
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.111
diff -c -c -r1.111 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 5 Jan 2009 16:54:37 - 1.111
--- src/bin/pg_dump/pg_dumpall.c 6 Jan 2009 18:01:07 -
***
*** 500,506
" fail after waiting TIMEOUT for a table lock\n"));
printf(_("\nOptions controlling the output content:\n"));
printf(_(" -a, --data-only dump only the data, not the schema\n"));
! printf(_(" -c, --clean clean (drop) databases prior to create\n"));
printf(_(" -d, --insertsdump data as INSERT, rather than COPY, commands\n"));
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
printf(_(" -g, --globals-only dump only global objects, no databases\n"));
--- 500,506
" fail after waiting TIMEOUT for a table lock\n"));
printf(_("\nOptions controlling the output content:\n"));
printf(_(" -a, --data-only dump only the data, not the schema\n"));
! printf(_(" -c, --clean clean (drop) databases before recreating\n"));
printf(_(" -d, --insertsdump data as INSERT, rather than COPY, commands\n"));
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
printf(_(" -g, --globals-only dump only global objects, no databases\n"));
--
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] Significantly larger toast tables on 8.4?
On Monday 05 January 2009 18:45:49 Alvaro Herrera wrote: > I did some measurements months ago, and it was very clear that libz > compression was a lot tighter than the PGLZ code. Back to the issue at hand. The question at the top of the thread was which of the following behaviors we'd like by default: (1) Compress everything within reason by default, causing slower retrieval, do not offer substr optimization. [<= 8.3] (2) Compress only up to 1 MB, causing faster retrieval, supporting substr optimization. [8.4devel] I am personally completely puzzled by option number 2. Is there even a single use case for that? -- 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] dblink vs SQL/MED - security and implementation details
On Tuesday 06 January 2009 19:50:51 Tom Lane wrote: > Peter Eisentraut writes: > > I think you want some permission checking on fdtest then, right? > > What about the permissions on the system catalogs themselves? > AFAICT, the pg_user_mappings view will expose user passwords to > the "owner" of the foreign server, which doesn't seem good. Well, no one is forcing you to put a password there. dblink has had its mechanisms for obtaining passwords until now, and those are not invalidated by this. There are as always limited use cases for hardcoding passwords, but in a fully multiuser environment you probably want to use a different authentication mechanism. Eventually, when we allow these modules to actually call out, we will have to seriously evaluate that. But for right now, if you don't want your password in there, don't put it there. -- 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] dblink vs SQL/MED - security and implementation details
Peter Eisentraut writes: > On Tuesday 06 January 2009 19:50:51 Tom Lane wrote: >> What about the permissions on the system catalogs themselves? >> AFAICT, the pg_user_mappings view will expose user passwords to >> the "owner" of the foreign server, which doesn't seem good. > Well, no one is forcing you to put a password there. dblink has had its > mechanisms for obtaining passwords until now, and those are not invalidated > by this. There are as always limited use cases for hardcoding passwords, but > in a fully multiuser environment you probably want to use a different > authentication mechanism. Eventually, when we allow these modules to > actually call out, we will have to seriously evaluate that. But for right > now, if you don't want your password in there, don't put it there. Huh? The advertised reason for putting in all this stuff was to provide a thought-through, secure mechanism for dealing with connection information. If we haven't done that thinking yet, I'm of the opinion the whole thing should be ripped out until we have. It's of exactly zero value if it cannot be trusted with a password. 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] version() output vs. 32/64 bits
Bruce Momjian wrote: > Tom Lane wrote: >> Bruce Momjian writes: >>> So what do we want to do for 8.4? Add 32/64-bit version() indicator and >>> add OUT parameters to the TODO list? >> +1. There seems a good case for making the 32/64bit distinction >> visible somewhere, and the text version string is as good as anyplace. > > OK, done with the attached patch, and autoconf run. Magnus, would you > add this change to the MSVC build? Thanks. > > test=> select version(); > version > -- > >PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > (1 row) > > Done. postgres=# select version(); version PostgreSQL 8.4devel, compiled by Visual C++ build 1400, 32-bit (1 row) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Is it really such a great idea for spi.h to include the world?
executor/spi.h includes far more than it needs, starting with postgres.h which as a general rule we don't expect any other header file to include. I think the argument for this was to keep things simple for SPI-using loadable modules, but I doubt that it's really improving their lives much. A quick look through the existing files that include spi.h shows that most of them have to include a pile of other stuff anyway. I propose changing spi.h to follow the same include-only-what-you-must rule as every other backend header file. Thoughts? 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] SPI nesting in plperl
I wrote:
> I tried fixing this
> http://archives.postgresql.org/pgsql-general/2009-01/msg00030.php
> by inserting SPI_push/SPI_pop calls around plperl's use of
> InputFunctionCall and OutputFunctionCall ...
> I also thought about attacking the problem by having InputFunctionCall
> and OutputFunctionCall automatically do SPI_push/SPI_pop if they are
> called within an active SPI context. I don't like this approach too
> much because it seems likely to mask bugs as often as fix them. (In
> particular I'd be afraid to back-patch such a change.) It might be the
> cleanest solution overall, though, particularly when you consider that
> we've probably got similar issues in pltcl, plpython, and add-on PLs.
I've done a trial patch along the second line, and on the whole I think
it's probably far safer than sprinkling the system with SPI_push/SPI_pop
calls. Comments?
regards, tom lane
Index: src/backend/executor/spi.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/spi.c,v
retrieving revision 1.204
diff -c -r1.204 spi.c
*** src/backend/executor/spi.c 2 Jan 2009 20:42:00 - 1.204
--- src/backend/executor/spi.c 6 Jan 2009 18:40:54 -
***
*** 296,301
--- 296,326
_SPI_curid--;
}
+ /* Conditional push: push only if we're inside a SPI procedure */
+ bool
+ SPI_push_conditional(void)
+ {
+ boolpushed = (_SPI_curid != _SPI_connected);
+
+ if (pushed)
+ {
+ _SPI_curid++;
+ /* We should now be in a state where SPI_connect would succeed
*/
+ Assert(_SPI_curid == _SPI_connected);
+ }
+ return pushed;
+ }
+
+ /* Conditional pop: pop only if SPI_push_conditional pushed */
+ void
+ SPI_pop_conditional(bool pushed)
+ {
+ /* We should be in a state where SPI_connect would succeed */
+ Assert(_SPI_curid == _SPI_connected);
+ if (pushed)
+ _SPI_curid--;
+ }
+
/* Restore state of SPI stack after aborting a subtransaction */
void
SPI_restore_connection(void)
Index: src/backend/utils/fmgr/fmgr.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/fmgr/fmgr.c,v
retrieving revision 1.124
diff -c -r1.124 fmgr.c
*** src/backend/utils/fmgr/fmgr.c 1 Jan 2009 17:23:51 - 1.124
--- src/backend/utils/fmgr/fmgr.c 6 Jan 2009 18:40:55 -
***
*** 1846,1861
--- 1851,1875
* the caller should assume the result is NULL, but we'll call the input
* function anyway if it's not strict. So this is almost but not quite
* the same as FunctionCall3.
+ *
+ * One important difference from the bare function call is that we will
+ * push any active SPI context, allowing SPI-using I/O functions to be
+ * called from other SPI functions without extra notation. This is a hack,
+ * but the alternative of expecting all SPI functions to do SPI_push/SPI_pop
+ * around I/O calls seems worse.
*/
Datum
InputFunctionCall(FmgrInfo *flinfo, char *str, Oid typioparam, int32 typmod)
{
FunctionCallInfoData fcinfo;
Datum result;
+ boolpushed;
if (str == NULL && flinfo->fn_strict)
return (Datum) 0; /* just return null result */
+ pushed = SPI_push_conditional();
+
InitFunctionCallInfoData(fcinfo, flinfo, 3, NULL, NULL);
fcinfo.arg[0] = CStringGetDatum(str);
***
*** 1881,1886
--- 1895,1902
fcinfo.flinfo->fn_oid);
}
+ SPI_pop_conditional(pushed);
+
return result;
}
***
*** 1889,1901
*
* Do not call this on NULL datums.
*
! * This is mere window dressing for FunctionCall1, but its use is recommended
! * anyway so that code invoking output functions can be identified easily.
*/
char *
OutputFunctionCall(FmgrInfo *flinfo, Datum val)
{
! return DatumGetCString(FunctionCall1(flinfo, val));
}
/*
--- 1905,1926
*
* Do not call this on NULL datums.
*
! * This is almost just window dressing for FunctionCall1, but it includes
! * SPI context pushing for the same reasons as InputFunctionCall.
*/
char *
OutputFunctionCall(FmgrInfo *flinfo, Datum val)
{
! char *result;
! boolpushed;
!
! pushed = SPI_push_conditional();
!
! result = DatumGetCString(FunctionCall1(flinfo, val));
!
! SPI_pop_conditional(pushed);
!
! return result;
}
/*
***
*** 1904,1910
* "buf" may be NULL to indicate we are reading a NULL. In this case
* the caller should assume the result is NULL, but we'll call the receive
* function anyway if it's not strict. So this is almost but not quite
! * the same as FunctionCall3.
*/
Datum
ReceiveFunctionCall(FmgrInfo *flinfo, StringI
Re: [HACKERS] dblink vs SQL/MED - security and implementation details
Tom Lane wrote: > Peter Eisentraut writes: >> I think you want some permission checking on fdtest then, right? > > What about the permissions on the system catalogs themselves? > AFAICT, the pg_user_mappings view will expose user passwords to > the "owner" of the foreign server, which doesn't seem good. > Usually it would have been the server owner who created those user mappings in the first place -- so the passwords are already known to him/her. Of course it is possible to create the mappings first and later change the ownership of the server, thus exposing the passwords to a new role. But IMHO, it would be reasonable to assume that the owner of the server has full control over its user mappings. regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is it really such a great idea for spi.h to include the world?
Tom Lane wrote: > executor/spi.h includes far more than it needs, starting with postgres.h > which as a general rule we don't expect any other header file to > include. I think the argument for this was to keep things simple for > SPI-using loadable modules, but I doubt that it's really improving their > lives much. A quick look through the existing files that include spi.h > shows that most of them have to include a pile of other stuff anyway. > > I propose changing spi.h to follow the same include-only-what-you-must > rule as every other backend header file. Thoughts? I don't think we ever cleaned out spi.h in the past because we were worried about 3rd party code using it (I am fine with a cleanup). -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] PostgreSQL 8.3.4 reproducible crash
Dmitry Koterov wrote: > Hello. > > Here is the SQL to reproduce the server crash: > > > CREATE SCHEMA bug1 AUTHORIZATION postgres; > > SET search_path = bug1, pg_catalog; > > CREATE FUNCTION bug1.domain_check (integer) RETURNS boolean > AS > $body$ > SELECT $1 <> 0 > $body$ > LANGUAGE sql IMMUTABLE STRICT; > > CREATE DOMAIN bug1."domain" AS integer > CONSTRAINT "check" CHECK (bug1.domain_check(VALUE)); > > CREATE TYPE bug1.composite AS ( > id domain > ); > > select '(1)'::bug1.composite; This has been fixed in CVS HEAD but I am unsure if and how far it was backpatched. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] dblink vs SQL/MED - security and implementation details
Peter Eisentraut wrote:
> On Tuesday 06 January 2009 05:54:14 Joe Conway wrote:
>> contrib_regression=> SELECT dblink_connect('myconn', 'fdtest');
>> dblink_connect
>>
>> OK
>> (1 row)
>
> I think you want some permission checking on fdtest then, right?
>
The proposed "connection lookup" functions have USAGE check on the
server.
About the connstr validation -- it would be best done in the connection
lookup function. IMO it would make sense to validate the connstring if the
foreign server is not OWNED by a superuser. This would enable less trusted
to create and own servers but would force them to provide a username and
password (validate in CreateUserMapping and GetForeignConnectionOptions).
And superuser could still set up a connection that makes use of .pgpass,
pgservice etc. Comments?
regards,
Martin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Warning about the 8.4 release
Bruce Momjian writes: > Peter Eisentraut wrote: >> Most of the entries are quite frankly junk, > That was the same reaction Tom had. Again, many might be junk, but is > it 100% junk. What about: > 8.4 - psql output for \l Done (and this is on the commitfest page anyway) > Overriding Kerberos parameters Just submitted 4 days ago, and anyway Magnus is quite capable of committing it for himself > HAVE_FSEEKO for WIN32 Proposes moving win32-only code into port.h, which is 100% wrong IMHO > stat() vs cygwin According to the thread, no one but you thinks there's a problem. > Memory mess introduced by recent funcapi.c patch Fixed > The suppress_redundant_updates_trigger() works incorrectly Fixed > So what's an "empty" array anyway? Blue-sky discussion about a fundamental behavior change that should certainly not be put into 8.4 in a rush. As-is, this list is completely unhelpful. It looks like you've dumped all your unread mail onto this page and asked the rest of us to sort it for you. I'm sorry, but I've got other things to do. 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] Is it really such a great idea for spi.h to include the world?
Bruce Momjian wrote: > Tom Lane wrote: > > executor/spi.h includes far more than it needs, starting with postgres.h > > which as a general rule we don't expect any other header file to > > include. I think the argument for this was to keep things simple for > > SPI-using loadable modules, but I doubt that it's really improving their > > lives much. A quick look through the existing files that include spi.h > > shows that most of them have to include a pile of other stuff anyway. > > > > I propose changing spi.h to follow the same include-only-what-you-must > > rule as every other backend header file. Thoughts? > > I don't think we ever cleaned out spi.h in the past because we were > worried about 3rd party code using it (I am fine with a cleanup). I've wondered about spi.h lately too while looking at header cleanup, and I agree with the proposed solution. The worst that can happen is that somebody needs to add extra includes in their programs in order for them to compile with 8.4. We do enough other changes that this one is really minor. Better late than never anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is it really such a great idea for spi.h to include the world?
Alvaro Herrera writes: > Bruce Momjian wrote: >> Tom Lane wrote: >>> I propose changing spi.h to follow the same include-only-what-you-must >>> rule as every other backend header file. Thoughts? >> >> I don't think we ever cleaned out spi.h in the past because we were >> worried about 3rd party code using it (I am fine with a cleanup). > I've wondered about spi.h lately too while looking at header cleanup, > and I agree with the proposed solution. The worst that can happen is > that somebody needs to add extra includes in their programs in order for > them to compile with 8.4. We do enough other changes that this one is > really minor. Better late than never anyway. Okay, I'll do a trial patch and we can see exactly how much has to be added (at least among core and contrib) before deciding for sure. 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] stat() vs cygwin
Andrew Dunstan wrote: > > > Bruce Momjian wrote: > > Andrew Dunstan wrote: > > > >> Alvaro Herrera wrote: > >> > >>> Andrew Dunstan wrote: > >>> > >>> > I'm confused. There is a Cygwin member of buildfarm, working quite > happily. Can you point me to the exact patch in question, please? I > thought we resolved the matter of stat() ages ago. > > > >>> http://archives.postgresql.org/message-id/4865F707.6010702%40x-ray.at > >>> > >>> > >>> > >> That patch is NOT about $subject. In fact, if you read that whole thread > >> you will see here > >> http://archives.postgresql.org/pgsql-hackers/2008-06/msg00915.php that I > >> conducted a test on Cygwin and found it was not suffering from the > >> problem we fixed on WIN32. > >> > >> AFAICT Reini's patch is about fixing OpenSSL and possibly some other > >> options on Cygwin. It was rejected because it had other problems, but is > >> not indicative of a fundamental problem on Cygwin. There is no reason I > >> am aware of that we should declare Cygwin no longer supported, no matter > >> how much its continued existence apparently annoys a few people :-) . > >> > > > > Oh, good, thanks for clearing that up. So should we just document that > > OpenSSL doesn't work on Cygwin and call this item closed? > > > > > > This item should be closed. We should see if Reini can submit an > acceptable patch for OpenSSL. I have documented that OpenSSL is not supported for Cygwin. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Warning about the 8.4 release
Tom Lane wrote: > Bruce Momjian writes: > > Peter Eisentraut wrote: > >> Most of the entries are quite frankly junk, > > > That was the same reaction Tom had. Again, many might be junk, but is > > it 100% junk. What about: > > > 8.4 - psql output for \l > > Done (and this is on the commitfest page anyway) Did you respond to this comment? http://archives.postgresql.org/pgsql-hackers/2009-01/msg00154.php > > HAVE_FSEEKO for WIN32 > > Proposes moving win32-only code into port.h, > which is 100% wrong IMHO The problem is that we override fseeko only in pg_dump, while it should be done globally for Win32; that is what Andrew was asking about. Am I missing something? > > stat() vs cygwin > > According to the thread, no one but you thinks there's a > problem. Well, I offered to document the problem, and have done that now. > > Memory mess introduced by recent funcapi.c patch > > Fixed OK. > > The suppress_redundant_updates_trigger() works incorrectly > > Fixed OK. > > So what's an "empty" array anyway? > > Blue-sky discussion about a fundamental behavior change > that should certainly not be put into 8.4 in a rush. Added to TODO. > As-is, this list is completely unhelpful. It looks like you've dumped > all your unread mail onto this page and asked the rest of us to sort it > for you. I'm sorry, but I've got other things to do. That explains why people were confused. I never intended for the list to be scanned --- I only wanted to give people and idea of the _volume_ I am looking at; I will approach people individually to close them, though the comments I did get were very helpful. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Significantly larger toast tables on 8.4?
Peter Eisentraut wrote: >(1) Compress everything within reason by default, causing slower retrieval, do >not offer substr optimization. [<= 8.3] >(2) Compress only up to 1 MB, causing faster retrieval, supporting substr >optimization. [8.4devel] >I am personally completely puzzled by option number 2. Is there even a single >use case for that? I can't imagine one, and (in this thread at least) noone has demonstrated such; Tom hinted at one, but he didn't elaborate. -- Sincerely, Stephen R. van den Berg. "Very funny, Mr. Scott. Now beam down my clothes!" -- 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] dblink vs SQL/MED - security and implementation details
Peter Eisentraut wrote:
On Tuesday 06 January 2009 05:54:14 Joe Conway wrote:
--
-- now as untrusted user dblink_regression_test
--
contrib_regression=> SELECT dblink_connect('myconn', 'fdtest');
dblink_connect
OK
(1 row)
I think you want some permission checking on fdtest then, right?
I don't see anything documented under GRANT which controls privileges on
a mapping, and the USAGE on a server only controls what a user can see
by query. I assume that if the superuser creates a mapping from user foo
to server bar, foo can still use bar via the mapping, even if they don't
have USAGE granted on the server. It isn't clear from the docs what is
intended, so I could have that wrong.
But even if foo is granted USAGE on bar, I think you miss the point. If you:
1. grant a non-superuser (foo) access to a server (bar)
2. create a mapping for foo to bar which includes no password
3. configure bar to not require authentication (trust)
you will get the privilege escalation as shown (e.g. foo becomes
postgres on bar).
Joe
--
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] Significantly larger toast tables on 8.4?
On Tue, Jan 6, 2009 at 12:57, Stephen R. van den Berg wrote: > Peter Eisentraut wrote: >>(1) Compress everything within reason by default, causing slower retrieval, do >>not offer substr optimization. [<= 8.3] > >>(2) Compress only up to 1 MB, causing faster retrieval, supporting substr >>optimization. [8.4devel] > >>I am personally completely puzzled by option number 2. Is there even a single >>use case for that? > > I can't imagine one, and (in this thread at least) noone has demonstrated > such; Tom hinted at one, but he didn't elaborate. Well that check got removed today anyway see: http://archives.postgresql.org/pgsql-committers/2009-01/msg00069.php -- 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] incoherent view of serializable transactions
> Kevin Grittner wrote: Paul Schlie wrote: >> Sorry if I'm restating the obvious, however I don't understand the >> confusion, as it seems the standard's definition isn't mysterious; >> it simply requires that the resulting state from the concurrent >> execution of transactions (and implicitly any subset) designated to >> occur at the isolation level SERIALIZABLE be equivalent to SOME >> LITERALLY SERIAL execution of said transactions. > > I think that some of the confusion may result from changes in the > standard. As far as I can recall, the language requiring that the > SERIALIZABLE transaction isolation level be truly serializable was not > in early versions of the standard, and it may be that there is some > reluctance to concede that a shift in the standard has rendered > PostgreSQL out of compliance on this point. > > As I see it, the discussion on this thread is around recognition of > the requirements of the current standard within the PostgreSQL > documentation. > > There is a related thread on which I'm attempting to come up with > documentation to assist those familiar with true serializable behavior > who are attempting to recognize application coding patterns where the > differences between that and snapshot isolation are material, with > tips on how to handle these differences. There seems to be some > question whether the patterns in which anomalies occur are common > enough to merit comment. > > If you could reference any concise and accessible work on these > anomalies and practical workarounds in application code, it would be > much appreciated. Personally; although compliance may reduce the execution performance of such so designated transactions, it will correspondingly warrant correct results, and should be the goal rather than documenting non-conformance; as those who wish to embed more direct control over transaction evaluation into their specification to enable their improved concurrent execution efficiency by utilizing more relaxed evaluation semantics, remain free to do without penalty. (Simple examples of the risk of non-compliance already seem sufficiently identified in your example and first reference cited). Merely documenting that transactions designated to be evaluated at the isolation level SERIALIZABLE may not yield expected results, as currently identified, seems sufficient in the short term; and as/if enough interest develops otherwise, so may an effort to warrant compliance; I suspect. (as that known to most often be fine, can't be relied upon in practice) -- 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] Is it really such a great idea for spi.h to include the world?
I wrote:
> Okay, I'll do a trial patch and we can see exactly how much has to be
> added (at least among core and contrib) before deciding for sure.
This compiles and passes regression tests. It looks like the main
things there might be an argument for adding back to spi.h would be
pg_type.h and builtins.h, as a very large proportion of the files
using spi.h had to have those added. Comments?
regards, tom lane
Index: contrib/spi/autoinc.c
===
RCS file: /cvsroot/pgsql/contrib/spi/autoinc.c,v
retrieving revision 1.15
diff -c -r1.15 autoinc.c
*** contrib/spi/autoinc.c 17 May 2008 01:28:22 - 1.15
--- contrib/spi/autoinc.c 6 Jan 2009 20:38:28 -
***
*** 1,10
/*
* $PostgreSQL: pgsql/contrib/spi/autoinc.c,v 1.15 2008/05/17 01:28:22
adunstan Exp $
*/
! #include "executor/spi.h" /* this is what you need to work with
SPI */
! #include "commands/trigger.h" /* -"- and triggers */
! #include "commands/sequence.h"/* for nextval() */
PG_MODULE_MAGIC;
--- 1,13
/*
* $PostgreSQL: pgsql/contrib/spi/autoinc.c,v 1.15 2008/05/17 01:28:22
adunstan Exp $
*/
+ #include "postgres.h"
! #include "catalog/pg_type.h"
! #include "commands/sequence.h"
! #include "commands/trigger.h"
! #include "executor/spi.h"
! #include "utils/builtins.h"
PG_MODULE_MAGIC;
Index: contrib/spi/insert_username.c
===
RCS file: /cvsroot/pgsql/contrib/spi/insert_username.c,v
retrieving revision 1.16
diff -c -r1.16 insert_username.c
*** contrib/spi/insert_username.c 25 Mar 2008 22:42:42 - 1.16
--- contrib/spi/insert_username.c 6 Jan 2009 20:38:28 -
***
*** 6,15
* insert user name in response to a trigger
* usage: insert_username (column_name)
*/
! #include "executor/spi.h" /* this is what you need to work with
SPI */
! #include "commands/trigger.h" /* -"- and triggers */
! #include "miscadmin.h"/* for GetUserName() */
PG_MODULE_MAGIC;
--- 6,18
* insert user name in response to a trigger
* usage: insert_username (column_name)
*/
+ #include "postgres.h"
! #include "catalog/pg_type.h"
! #include "commands/trigger.h"
! #include "executor/spi.h"
! #include "miscadmin.h"
! #include "utils/builtins.h"
PG_MODULE_MAGIC;
Index: contrib/spi/moddatetime.c
===
RCS file: /cvsroot/pgsql/contrib/spi/moddatetime.c,v
retrieving revision 1.14
diff -c -r1.14 moddatetime.c
*** contrib/spi/moddatetime.c 1 Feb 2007 19:10:23 - 1.14
--- contrib/spi/moddatetime.c 6 Jan 2009 20:38:28 -
***
*** 13,21
Jan Wieck who told me about the timestamp_in("now")
function.
OH, me, I'm Terry Mackintosh
*/
! #include "executor/spi.h" /* this is what you need to work with
SPI */
! #include "commands/trigger.h" /* -"- and triggers */
PG_MODULE_MAGIC;
--- 13,23
Jan Wieck who told me about the timestamp_in("now")
function.
OH, me, I'm Terry Mackintosh
*/
+ #include "postgres.h"
! #include "catalog/pg_type.h"
! #include "executor/spi.h"
! #include "commands/trigger.h"
PG_MODULE_MAGIC;
Index: contrib/spi/refint.c
===
RCS file: /cvsroot/pgsql/contrib/spi/refint.c,v
retrieving revision 1.33
diff -c -r1.33 refint.c
*** contrib/spi/refint.c17 May 2008 01:28:22 - 1.33
--- contrib/spi/refint.c6 Jan 2009 20:38:28 -
***
*** 5,16
* refint.c --set of functions to define referential integrity
*constraints using general triggers.
*/
- #include "executor/spi.h" /* this is what you need to work with
SPI */
-
- #include "commands/trigger.h" /* -"- and triggers */
#include
PG_MODULE_MAGIC;
--- 5,17
* refint.c --set of functions to define referential integrity
*constraints using general triggers.
*/
+ #include "postgres.h"
#include
+ #include "commands/trigger.h"
+ #include "executor/spi.h"
+ #include "utils/builtins.h"
PG_MODULE_MAGIC;
Index: contrib/spi/timetravel.c
===
RCS file: /cvsroot/pgsql/contrib/spi/timetravel.c,v
retrieving revision 1.29
diff -c -r1.29 timetravel.c
*** contrib/spi/timetravel.c17 May 2008 01:28:22 - 1.29
--- contrib/spi/timetravel.c6 Jan 2009 20:38:28 -
***
*** 4,22
*
* timetravel.c --function to get time travel feature
*using general triggers.
*/
! /* Modified by BÖJTHE Zoltán, Hungary, mailto:urdes...@axelero.hu */
! #include "executor/spi.h" /* this is what you need to work with
SPI */
! #inclu
Re: [HACKERS] Warning about the 8.4 release
On Tuesday 06 January 2009 18:49:00 Bruce Momjian wrote: > If people have further updates please, please send them (with subject > headings please). Most of the entries are quite frankly junk, either already committed, already rejected, patches not under consideration, irrelevant discussions, or completely silly, e.g., 261. Warning about the 8.4 release -- 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] Warning about the 8.4 release
Peter Eisentraut wrote: > On Tuesday 06 January 2009 18:49:00 Bruce Momjian wrote: > > If people have further updates please, please send them (with subject > > headings please). > > Most of the entries are quite frankly junk, either already committed, already > rejected, patches not under consideration, irrelevant discussions, or > completely silly, e.g., > > 261. Warning about the 8.4 release That was the same reaction Tom had. Again, many might be junk, but is it 100% junk. What about: 8.4 - psql output for \l Overriding Kerberos parameters HAVE_FSEEKO for WIN32 stat() vs cygwin Memory mess introduced by recent funcapi.c patch The suppress_redundant_updates_trigger() works incorrectly So what's an "empty" array anyway? I just picked those at random. And again, though they are not required for the release, they appeared during 8.4 development and should be fixed now, if possible. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Is it really such a great idea for spi.h to include the world?
Tom Lane wrote: > I wrote: > > Okay, I'll do a trial patch and we can see exactly how much has to be > > added (at least among core and contrib) before deciding for sure. > > This compiles and passes regression tests. It looks like the main > things there might be an argument for adding back to spi.h would be > pg_type.h and builtins.h, as a very large proportion of the files > using spi.h had to have those added. Comments? They are both very lean, so no objections. I guess that the pg_type.h inclusion is needed due to the predefined type OIDs, and it makes me wonder whether it would be useful to have them in a separate header. Not enough concern for the idea to even make it to Bruce's open items mailbox ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel restore
On Mon, Dec 29, 2008 at 6:42 PM, Andrew Dunstan wrote: > > Attached is the latest parallel restore patch. I think this is getting > fairly close. > hi, i was making some tests in windows... but for some reason the pg_restore simply hangs... i'm using: pg_restore -f mic.backup -Fc -v -m5 there is a way to know if it's really hanging or is simply too slow? i plan to let it run all night long just in case... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel restore
On Tue, Jan 6, 2009 at 4:04 PM, Jaime Casanova wrote: > On Mon, Dec 29, 2008 at 6:42 PM, Andrew Dunstan wrote: >> >> Attached is the latest parallel restore patch. I think this is getting >> fairly close. >> > > hi, i was making some tests in windows... > anyway, when i try it, it prints on the screen "pgoff_t: 8, long:4" maybe a debugging print you have to remove -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] incoherent view of serializable transactions
>>> Paul Schlie wrote: >> Kevin Grittner wrote: >> There is a related thread on which I'm attempting to come up with >> documentation to assist those familiar with true serializable >> behavior who are attempting to recognize application coding >> patterns where the differences between that and snapshot isolation >> are material, with tips on how to handle these differences. There >> seems to be some question whether the patterns in which anomalies >> occur are common enough to merit comment. >> >> If you could reference any concise and accessible work on these >> anomalies and practical workarounds in application code, it would >> be much appreciated. > > Personally; although compliance may reduce the execution performance > of such so designated transactions, it will correspondingly warrant > correct results, and should be the goal rather than documenting > non-conformance; as those who wish to embed more direct control over > transaction evaluation into their specification to enable their > improved concurrent execution efficiency by utilizing more relaxed > evaluation semantics, remain free to do without penalty. (Simple > examples of the risk of non-compliance already seem sufficiently > identified in your example and first reference cited). > > Merely documenting that transactions designated to be evaluated at > the isolation level SERIALIZABLE may not yield expected results, as > currently identified, seems sufficient in the short term; and as/if > enough interest develops otherwise, so may an effort to warrant > compliance; I suspect. > > (as that known to most often be fine, can't be relied upon in > practice) Thank you for your perspective. I'm not sure that I totally followed you, so let me restate to see if it sounds right to you. You are suggesting that minimal discussion of the problem, the initial example I provided, and more discussion of how to ensure correct semantics would be what is needed? Filling in more detail if interest is expressed by users? If so, the draft of a partial replacement for the partial replacement of text in "Serializable Isolation versus True Serializability" may be close to what you're suggesting -- if additional guidance on when to use what additional locks is provided. I'll paste below my signature for comment. It's a little rough yet, but looking to see if I'm on the right track. The first paragraph is a slightly modified form of a suggestion from Robert Haas in: http://archives.postgresql.org/pgsql-hackers/2008-12/msg01732.php -Kevin PostgreSQL's MVCC framework, snapshot isolation, and limited automatic row-level locking permit a greater degree of concurrency than some other databases; however, even when the transaction isolation level is set to serializable, serialization anomalies can occur in some situations. When it is important to prevent these anomalies, explicit row-level or table-level locking can be used at the expense of reduced concurrency. Since PostgreSQL protects a serializable transaction against changes in the view of the data, and uses locks to prevent modification of data which is being modified by a concurrent transaction, the anomalies can only occur when a transaction reads data which is modified by a concurrent transaction, and uses that as the basis of database modifications which are read by a concurrent transaction. Data consistency checks at the application level have a problem with this in general, and are addressed in section 13.4. Some examples of other types of anomalies follow, with suggestions on how to use explicit locking to prevent the anomalies where needed. Consider a system which involves recording receipts, each of which must go into a daily deposit. There is a control table with one row containing the current deposit date for receipts. Each transaction which is inserting a receipt selects the deposit date from the control table within its transaction, and uses it for the receipt's deposit date. Somewhere mid-afternoon the control table's date is updated, all subsequent receipts should fall into the new day, and a report is run listing the receipts for the day and giving the deposit total. If all transactions involved were truly serializable, any SELECT of receipts for a date prior to the deposit date of the control table would see the complete, final set of receipts. Under the PostgreSQL implementation, unless explicit locking is used, although data eventually gets to that state there can be a window of time during which a SELECT can return an incomplete list of receipts for a date which appears to be closed, even if all transactions for modifying and viewing data are SERIALIZABLE. This window of time runs from the commit of the transaction which updated the control table until the commit of any pending transactions which are inserting receipts and which obtained a snapshot before the update of the control table. To prevent this anomaly, a lock can be taken out on the receipt ta
Re: [HACKERS] parallel restore
Jaime Casanova wrote: On Mon, Dec 29, 2008 at 6:42 PM, Andrew Dunstan wrote: Attached is the latest parallel restore patch. I think this is getting fairly close. hi, i was making some tests in windows... but for some reason the pg_restore simply hangs... i'm using: pg_restore -f mic.backup -Fc -v -m5 there is a way to know if it's really hanging or is simply too slow? i plan to let it run all night long just in case... Strange. Maybe the server log will show activity? cheers andrew -- 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
> Here's an updated version of the psql backslash patch that should > apply cleanly to the current HEAD. To recap, this makes all the \dX > commands (most importantly to most: \df) work like \dt does, in that it > requires a \dXS to see system items. See the archives for much more > discussion on the issue. Patch applied, thanks. --- Greg Sabino Mullane wrote: -- Start of PGP signed section. > > > 2. the help.c patch no longer applies > > > > 3. the help.c patch breaks alignment of the help output > > Attached is a patch to fix problems 2 and 3: help.c clean application and > formatting of the output therein. I also put \z right after \dp and removed > the duplicate wording, to make it fit better, per comments in this thread. > > -- > Greg Sabino Mullane [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Is it really such a great idea for spi.h to include the world?
Alvaro Herrera writes: > They are both very lean, so no objections. I guess that the pg_type.h > inclusion is needed due to the predefined type OIDs, and it makes me > wonder whether it would be useful to have them in a separate header. > Not enough concern for the idea to even make it to Bruce's open items > mailbox ... After the header refactoring Zdenek did last year, there's not much reason to not just #include pg_type.h --- so I'd just as soon keep those macros together with the associated DATA lines. 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] parallel restore
Andrew Dunstan writes: > Jaime Casanova wrote: >> i'm using: >> pg_restore -f mic.backup -Fc -v -m5 > Strange. Maybe the server log will show activity? There's no connection info, so that should just print to stdout, and probably there is no point in any parallelism. I'm guessing the -m switch invokes code that fails to deal with this case. 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] dblink vs SQL/MED - security and implementation details
Martin Pihlak writes: > Usually it would have been the server owner who created those user > mappings in the first place -- so the passwords are already known > to him/her. Of course it is possible to create the mappings first > and later change the ownership of the server, thus exposing the > passwords to a new role. But IMHO, it would be reasonable to assume > that the owner of the server has full control over its user mappings. So the DBA should know his users' passwords for remote sites? That's not normally considered good security practice. If the passwords were encrypted strings it might be acceptable, but without some libpq changes I think they'd have to be cleartext :-( 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: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 06, 2009 at 11:13:59PM +0530, Gurjeet Singh wrote: > If we consider the second branch of UNION ALL of both the queries above, if > "select '' " yields a text column, then so should a "select * from (select > '')". The problem is ofcourse that "select ''" doesn't produce a text column in postgres. This generally works fine, except in the case of UNION where none of the branches provide the necessary type info. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] hist boundary duplicates bug in head and 8.3
"Nathan Boley" writes: > For heavy tailed distributions, it is possible for analyze to > duplicate histogram boundaries. I don't think this is a bug. You've got values that didn't make it into the MCV list, but nonetheless occupy multiple buckets' worth of space in the remainder of the distribution. They *should* appear multiple times in the histogram. If they didn't, the histogram would be understating their frequency. 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] parallel restore
On Tue, Jan 6, 2009 at 4:32 PM, Tom Lane wrote: > Andrew Dunstan writes: >> Jaime Casanova wrote: >>> i'm using: >>> pg_restore -f mic.backup -Fc -v -m5 > >> Strange. Maybe the server log will show activity? > > There's no connection info, so that should just print to stdout, and > probably there is no point in any parallelism. I'm guessing the -m > switch invokes code that fails to deal with this case. > ah! ok, i run the command in this way instead: pg_restore -p 54320 -Fc -v -d mic mic.backup (why i can't use -f?) and it works fine, then to test parallel restore i did pg_restore -p 54320 -Fc -v -m5 -d mic mic.backup but i forgot to clean up the database... of course it throws a lot of "$object_name already exists" messages and the last one was a little strange, it says: pg_restore: [archiver (db)] connection to database "public" failed: FATAL: database "public" does not exist but there isn't a "public" database in the backup... besides that, maybe, unrelated issue, it seems to work fine... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel restore
On Mon, Dec 29, 2008 at 6:42 PM, Andrew Dunstan wrote: > > Attached is the latest parallel restore patch. I think this is getting > fairly close. > mmm... seems this patch are two in one... you're adding --multi-thread and --truncate-before-load options where the second one seems to be an optimization... maybe it's better to split in two incremental patches? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel restore
Jaime Casanova wrote: On Mon, Dec 29, 2008 at 6:42 PM, Andrew Dunstan wrote: Attached is the latest parallel restore patch. I think this is getting fairly close. mmm... seems this patch are two in one... you're adding --multi-thread and --truncate-before-load options where the second one seems to be an optimization... maybe it's better to split in two incremental patches? Well, the only reason it was needed was because you can't run a parallel restore in a single transaction. If the whole restore is run in a single transaction then truncate before load should be unnecessary. But if people want it made more general I can split it out. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: This makes all the \dX commands (most importantly to most: \df)
Bruce Momjian wrote: > Tom Lane wrote: > > momj...@postgresql.org (Bruce Momjian) writes: > > > This makes all the \dX commands (most importantly to most: \df) work > > > like \dt does, in that it requires a \dXS to see system items. > > > > The lack of any documentation change is glaring. > > Oh, it sure does. I will work on that. OK, documentation added with the attached patch, applied. The documentation needed quite a bit of cleanup, independent of this patch, which I did. I also found a bug that \do didn't work because the AND system table check was being added to the LEFT JOIN and not to the WHERE clause (trigger display was also a problem). -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/psql-ref.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.214 diff -c -c -r1.214 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 19 Dec 2008 16:25:16 - 1.214 --- doc/src/sgml/ref/psql-ref.sgml 6 Jan 2009 22:48:33 - *** *** 815,822 ! \d [ pattern ] ! \d+ [ pattern ] --- 815,821 ! \d[S+] [ pattern ] *** *** 834,839 --- 833,840 more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table. + The letter S restricts the listing to system objects; without + S, only non-system objects are shown. *** *** 849,855 ! \da [ pattern ] --- 850,856 ! \da[S] [ pattern ] *** *** 857,870 return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. ! \db [ pattern ] ! \db+ [ pattern ] --- 858,873 return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. + The letter S restricts the listing + to system objects; without S, only + non-system objects are shown. ! \db[+] [ pattern ] *** *** 879,891 ! \dc [ pattern ] Lists all available conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. --- 882,896 ! \dc[S] [ pattern ] Lists all available conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. + The letter S restricts the listing to system objects; without + S, only non-system objects are shown. *** *** 905,917 ! \dd [ pattern ] Shows the descriptions of objects matching the pattern, or of all visible objects if no argument is given. But in either case, only objects that have a description are listed. (Object covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences, large objects), rules, and triggers.) For example: --- 910,924 ! \dd[S] [ pattern ] Shows the descriptions of objects matching the pattern, or of all visible objects if no argument is given. But in either case, only objects that have a description are listed. + The letter S restricts the listing to system objects; without + S, only non-system objects are shown. (Object covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences, large objects), rules, and triggers.) For example: *** *** 935,954 ! \dD [ pattern ] Lists all available domains. If pattern is specified, only matching domains are shown. ! \des [ pattern ] ! \des+ [ pattern ] Lists all foreign servers (mnemonic: external --- 942,962 ! \dD[S] [ pattern ] Lists all available domains.
Re: [HACKERS] Re: [COMMITTERS] pgsql: This makes all the \dX commands (most importantly to most: \df)
Bruce Momjian wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > > > momj...@postgresql.org (Bruce Momjian) writes: > > > > This makes all the \dX commands (most importantly to most: \df) work > > > > like \dt does, in that it requires a \dXS to see system items. > > > > > > The lack of any documentation change is glaring. > > > > Oh, it sure does. I will work on that. > > OK, documentation added with the attached patch, applied. The > documentation needed quite a bit of cleanup, independent of this patch, > which I did. > > I also found a bug that \do didn't work because the AND system table > check was being added to the LEFT JOIN and not to the WHERE clause > (trigger display was also a problem). Let me also say that that \d* display is ready to fall over from its own weight: Informational Modifiers: S = show system objects + = Additional detail \l[+]list all databases \d[S+] list tables, views, and sequences \d[S+] NAME describe table, view, sequence, or index \da[S] [PATTERN] list aggregate functions \db[+] [PATTERN] list tablespaces \dc[S] [PATTERN] list conversions \dC [PATTERN]list casts \dd [PATTERN]show comment for object \dd[S] [PATTERN] list comments on objects \dD[S] [PATTERN] list domains \des[+] [PATTERN]list foreign servers \deu[+] [PATTERN]list user mappings \dew[+] [PATTERN]list foreign-data wrappers \df[S+] [PATTERN]list functions \dF[+] [PATTERN] list text search configurations \dFd[+] [PATTERN]list text search dictionaries \dFp[+] [PATTERN]list text search parsers \dFt[+] [PATTERN]list text search templates \dg [PATTERN]list roles (groups) \di[S+] [PATTERN]list indexes \dl list large objects, same as \lo_list \dn[+] [PATTERN] list schemas \do[S] [PATTERN] list operators \dp [PATTERN]list table, view, and sequence access privileges \z [PATTERN] same as \dp \ds[S+] [PATTERN]list sequences \dt[S+] [PATTERN]list tables \dT[S+] [PATTERN]list data types \du [PATTERN]list roles (users) \dv[S+] [PATTERN]list views -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Runaway backend at 100% CPU PostgreSQL v8.3.5
I'm running Debian PostgreSQL v8.3.5-1 on x86 in 32-bit mode.
Every once in a while, some backends start taking 100% CPU, as can be seen
below in the excerpt from the process table:
27256 ?Ss 0:04 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/post
27299 ?Ss 0:00 \_ postgres: writer process
27300 ?Ss 0:00 \_ postgres: wal writer process
27301 ?Ss 0:00 \_ postgres: autovacuum launcher process
27302 ?Ss 0:26 \_ postgres: stats collector process
12076 ?Rs 5681:50 \_ postgres: cms cms 10.0.0.5(59125) PARSE
12853 ?Rs 5549:25 \_ postgres: cms cms 10.0.0.5(48437) PARSE
25985 ?Ss 0:00 \_ postgres: cms cms 10.0.0.18(45807) idle
25986 ?Ss 0:00 \_ postgres: cms cms 10.0.0.18(45808) idle
It seems that the backend is stuck in some kind of endless loop. Since
it's a production Debian server, the backend is not compiled with debugging
turned on. The best I can do is ltrace it, in hopes of someone recognising
the infinite sequence.
Ltracing the 12853 process reveals the following libc calls:
Label a:
[pid 12853] __sigsetjmp(0xbfec7000, 0, 0x852e740, 0xb5b3fc16, 0x85e9228) = 0
[pid 12853] strlen("ExecutorState") = 13
[pid 12853] strcpy(0x84fb0e8, "ExecutorState") = 0x84fb0e8
[pid 12853] malloc(8192) = 0x860da48
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fb060, "ExprContext") = 0x84fb060
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fbda0, "ExprContext") = 0x84fbda0
[pid 12853] strncpy(0x860e498, "parent", 64) = 0x860e498
[pid 12853] strncpy(0x860e500, "sid", 64)= 0x860e500
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x85cd810, "ExprContext") = 0x85cd810
[pid 12853] malloc(8256) = 0x8600a28
[pid 12853] memmove(0x860e738, 0x860e600, 40, 0, 0x82eada9) = 0x860e738
[pid 12853] strncpy(0x860e7f0, "parent", 64) = 0x860e7f0
[pid 12853] strncpy(0x860e858, "sid", 64)= 0x860e858
[pid 12853] strlen("SPI TupTable") = 12
[pid 12853] strcpy(0x85cd1b0, "SPI TupTable")= 0x85cd1b0
[pid 12853] malloc(8192) = 0x860fa50
[pid 12853] memmove(0x860e738, 0x860e600, 40, 0x830fab4, 0x84fb088) = 0x860e738
[pid 12853] free(0x8600a28) =
[pid 12853] free(0x860da48) =
[pid 12853] free(0x860fa50) =
[pid 12853] __sigsetjmp(0xbfec7000, 0, 0x852e740, 0xb5b3fc16, 0x85e9228) = 0
[pid 12853] strlen("ExecutorState") = 13
[pid 12853] strcpy(0x85cd1b0, "ExecutorState") = 0x85cd1b0
[pid 12853] malloc(8192) = 0x860da48
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fb0e8, "ExprContext") = 0x84fb0e8
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fb060, "ExprContext") = 0x84fb060
[pid 12853] strncpy(0x860e498, "parent", 64) = 0x860e498
[pid 12853] strncpy(0x860e500, "sid", 64)= 0x860e500
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fbda0, "ExprContext") = 0x84fbda0
[pid 12853] malloc(8256) = 0x8600a28
[pid 12853] memmove(0x860e738, 0x860e600, 40, 0, 0x82eada9) = 0x860e738
[pid 12853] strncpy(0x860e7f0, "parent", 64) = 0x860e7f0
[pid 12853] strncpy(0x860e858, "sid", 64)= 0x860e858
[pid 12853] strlen("SPI TupTable") = 12
[pid 12853] strcpy(0x85cd810, "SPI TupTable")= 0x85cd810
[pid 12853] malloc(8192) = 0x860fa50
[pid 12853] memmove(0x860e738, 0x860e600, 40, 0x830fab4, 0x85cd150) = 0x860e738
[pid 12853] free(0x8600a28) =
[pid 12853] free(0x860da48) =
[pid 12853] free(0x860fa50) =
[pid 12853] __sigsetjmp(0xbfec7000, 0, 0x852e740, 0xb5b3fc16, 0x85e9228) = 0
[pid 12853] strlen("ExecutorState") = 13
[pid 12853] strcpy(0x85cd810, "ExecutorState") = 0x85cd810
[pid 12853] malloc(8192) = 0x860da48
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x85cd1b0, "ExprContext") = 0x85cd1b0
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fb0e8, "ExprContext") = 0x84fb0e8
[pid 12853] strncpy(0x860e498, "parent", 64) = 0x860e498
[pid 12853] strncpy(0x860e500, "sid", 64)= 0x860e500
[pid 12853] strlen("ExprContext")= 11
[pid 12853] strcpy(0x84fb060, "ExprContext") = 0x84fb060
[pid 12853] malloc(8256) = 0x8600a28
[pid 12853] memmove(0x860e738, 0x860e600, 40, 0, 0x82eada9) = 0x860e738
[pid 12853] strncpy(0x860e7f0, "parent", 64) = 0x860e7f0
[pid 12853] strncpy(0x860e858, "sid", 64)= 0x860e858
[pid 12853] strlen("SPI TupTable")
Re: [HACKERS] Re: [COMMITTERS] pgsql: This makes all the \dX commands (most importantly to most: \df)
On Tue, 2009-01-06 at 18:08 -0500, Bruce Momjian wrote: > Bruce Momjian wrote: > > I also found a bug that \do didn't work because the AND system table > > check was being added to the LEFT JOIN and not to the WHERE clause > > (trigger display was also a problem). > > Let me also say that that \d* display is ready to fall over from its own > weight: > > Informational > Modifiers: S = show system objects + = Additional detail > \l[+]list all databases > \d[S+] list tables, views, and sequences > \d[S+] NAME describe table, view, sequence, or index > \da[S] [PATTERN] list aggregate functions > \db[+] [PATTERN] list tablespaces > \dc[S] [PATTERN] list conversions Hmmm, I wonder if it makes sense to make it four column instead of two column. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Runaway backend at 100% CPU PostgreSQL v8.3.5
"Stephen R. van den Berg" writes: > It seems that the backend is stuck in some kind of endless loop. Since > it's a production Debian server, the backend is not compiled with debugging > turned on. The best I can do is ltrace it, in hopes of someone recognising > the infinite sequence. Well, it seems to be repeatedly executing a SPI call, which suggests but doesn't prove that it's in a PL function ... but how would it get into one while in PARSE state? Anyway there's not much data here. You might try attaching to the backend process with gdb and trying "bt" and "p debug_query_string"; I think you should get something out of that even without debug symbols. If not, maybe turning on query logging would be worthwhile. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: This makes all the \dX commands (most importantly to most: \df)
Joshua D. Drake wrote:
> On Tue, 2009-01-06 at 18:08 -0500, Bruce Momjian wrote:
> > Bruce Momjian wrote:
>
> > > I also found a bug that \do didn't work because the AND system table
> > > check was being added to the LEFT JOIN and not to the WHERE clause
> > > (trigger display was also a problem).
> >
> > Let me also say that that \d* display is ready to fall over from its own
> > weight:
> >
> > Informational
> > Modifiers: S = show system objects + = Additional detail
> > \l[+]list all databases
> > \d[S+] list tables, views, and sequences
> > \d[S+] NAME describe table, view, sequence, or index
> > \da[S] [PATTERN] list aggregate functions
> > \db[+] [PATTERN] list tablespaces
> > \dc[S] [PATTERN] list conversions
>
> Hmmm, I wonder if it makes sense to make it four column instead of two
> column.
You mean like this?
fprintf(output, _("Informational\n"));
fprintf(output, _(" Modifiers: S = show system objects + = Additional
detail\n"));
fprintf(output, _(" \\l[+]list all databases\n"));
fprintf(output, _(" \\d[S+] list tables, views, and
sequences\n"));
fprintf(output, _(" \\d[S+] NAMEdescribe table, view, sequence,
or index\n"));
fprintf(output, _(" \\da[S] [PATTERN] list aggregate functions\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
fprintf(output, _(" \\dC [PATTERN] list casts\n"));
fprintf(output, _(" \\dd [PATTERN] show comment for object\n"));
fprintf(output, _(" \\dd[S] [PATTERN] list comments on objects\n"));
fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data wrappers\n"));
fprintf(output, _(" \\df[S+] [PATTERN] list functions\n"));
fprintf(output, _(" \\dF[+] [PATTERN] list text search
configurations\n"));
fprintf(output, _(" \\dFd[+] [PATTERN] list text search
dictionaries\n"));
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg [PATTERN] list roles (groups)\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same as
\\lo_list\n"));
fprintf(output, _(" \\dn[+] [PATTERN] list schemas\n"));
fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence
access privileges\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, "\n");
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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] hist boundary duplicates bug in head and 8.3
>> For heavy tailed distributions, it is possible for analyze to >> duplicate histogram boundaries. > > I don't think this is a bug. hmmm... Well, I assumed it was a bug from a comment in analyze. >From ( near ) line 2130 in analyze.c * least 2 instances in the sample. Also, we won't suppress values * that have a frequency of at least 1/K where K is the intended * number of histogram bins; such values might otherwise cause us to * emit duplicate histogram bin boundaries. */ If this is expected, I'm also not sure what the use of maxmincount in analyze is... Thanks for the response, Nathan -- 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] hist boundary duplicates bug in head and 8.3
"Nathan Boley" writes: >> I don't think this is a bug. > hmmm... Well, I assumed it was a bug from a comment in analyze. > From ( near ) line 2130 in analyze.c > * least 2 instances in the sample. Also, we won't suppress values > * that have a frequency of at least 1/K where K is the intended > * number of histogram bins; such values might otherwise cause us to > * emit duplicate histogram bin boundaries. That's talking about a case where we have a choice whether to include a value in the MCV list or not. Once the MCV list is maxed out, we can't do anything to avoid duplicates. 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
