Re: [HACKERS] security hook on authorization
On Mon, 25 Oct 2010 21:45:23 -0400 Robert Haas wrote: > On Mon, Oct 25, 2010 at 8:51 AM, KaiGai Kohei wrote: > > One possible candidate is CheckMyDatabase() that checks ACL_CONNECT > > permission for the required database prior to execution of all the > > queries. > > Currently, we don't have any security hook around here. > > But, if we have "InvokeSecurityHook()" here, it will be able to > > kill two birds with one stone. The 1st bird is getpeercon(3), and > > the 2nd bird is permission check on the selected database. > > Yeah, that might be a possibility. > > >> This is closer to the mark, but mostly speculative, and not detailed > >> enough to determine whether the proposed hook is properly located. ?It > >> seems rather early to me: this is before we've sent the authentication > >> packet to the client, so we couldn't, for example, log the success or > >> failure of the authentication; we don't know whether it will succeed > >> or fail. > >> > > Hmm. But the auth_failed() raises a fatal error, so we need to put > > a hook before the invocation to log a case of authentication failed. > > > > | + ? if (ClientAuthentication_hook) > > | + ? ? ? (*ClientAuthentication_hook)(port, status); > > | + > > | ? ? if (status == STATUS_OK) > > | ? ? ? ? sendAuthRequest(port, AUTH_REQ_OK); > > | ? ? else > > | ? ? ? ? auth_failed(port, status); > > > > Or, perhaps, we should modify this if-block to ensure the hook being > > called after sendAuthRequest() but before auth_failed(). > > Oh. You know, I am realizing that I misread this patch. This hook is > actually after authentication has been done; it's merely before we've > told the client what happened. So maybe this is committable as-is, > modulo some work on the comments. > OK, I'll re-submit my patch with a contrib module that utilizes the authentication hook. (Maybe, it just sleep a few seconds just after authentication failed.) A hook on CheckMyDatabase() might be a candidate of this feature and upcoming permission check. But it will be a future work, is't 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
Re: [HACKERS] Extensible executor nodes for preparation of SQL/MED
On Tue, Oct 26, 2010 at 12:28 AM, Tom Lane wrote:
> But it might be a good change anyway from a performance standpoint,
> in case a call through a function pointer is faster than a big switch.
> Have you tried benchmarking it on common platforms?
I didn't intend performance, but there is small but measurable win
in it if I avoided indirections. We might not always need to copy
a whole vtable into planstate; only ExecProcNode might be enough.
I'll continue the research.
24957.767 ms : master (a big switch)
25059.838 ms : two indirections (planstate->plan->vtable->fn)
24819.298 ms : one indirection (planstate->plan->vtable.fn)
24118.436 ms : direct call (planstate->vtable.fn)
So, major benefits of the change might be performance and code refactoring.
Does anyone have comments about it for the functionality? It might also be
used by SQL/MED and executor hooks, but I have no specific idea yet.
[measuring settings and queries]
=# SHOW shared_buffers;
shared_buffers
512MB
=# CREATE TABLE tbl AS SELECT i FROM generate_series(1, 1000) AS t(i);
=# VACUUM ANALYZE tbl;
=# SELECT count(*), pg_size_pretty(pg_relation_size('tbl')) FROM tbl;
=# CREATE FUNCTION test(n integer) RETURNS void LANGUAGE plpgsql AS
$$
DECLARE
i integer;
r bigint;
BEGIN
FOR i IN 1..n LOOP
SELECT count(*) INTO r FROM tbl;
END LOOP;
END;
$$;
=# \timing
=# SELECT test(30);
--
Itagaki Takahiro
--
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] Tab completion for view triggers in psql
On Tue, Oct 26, 2010 at 11:34 AM, David Fetter wrote: >> Do we need to 'add' it? > Possibly. My understanding is that it couldn't really replace it. Ah, I see. I was wrong. We can have modification privileges for views even if they have no INSTEAD OF triggers. So, I think your original patch is the best solution. We could use has_table_privilege() additionally, but we need to consider any other places if we use it. For example, DROP privileges, etc. -- Itagaki Takahiro -- 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] Tab completion for view triggers in psql
On Tue, Oct 26, 2010 at 11:10:53AM +0900, Itagaki Takahiro wrote:
> On Tue, Oct 26, 2010 at 10:53 AM, David Fetter wrote:
> >> How about has_table_privilege() to filter candidate relations
> >
> > That's orthogonal to tgtype (snip) Shall I send a new patch with
> > that added?
>
> Do we need to 'add' it?
Possibly. My understanding is that it couldn't really replace it.
> I intended to replace the JOIN with pg_trigger to
> has_table_privilege() (and relkind IN ('r', 'v')) for INSERT,
> UPDATE, and DELETE cases. Query_for_list_of_writeables might still
> require your patch, though.
My understanding is that there are two parts to this:
1. Does the view have the operation (INSERT, UPDATE, or DELETE)
defined on it at all?
2. Can the current role actually perform the operation defined?
If a view has at least one trigger, that view will have corresponding
entries in pg_trigger, and the tgtype entry determines which
operations have been defined, hence that EXISTS() query. This
establishes part 1.
The call to has_table_privilege() establishes part 2.
If I've misunderstood, please let me know :)
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tab completion for view triggers in psql
On Tue, Oct 26, 2010 at 10:53 AM, David Fetter wrote:
>> How about has_table_privilege() to filter candidate relations
>
> That's orthogonal to tgtype (snip)
> Shall I send a new patch with that added?
Do we need to 'add' it? I intended to replace the JOIN with pg_trigger
to has_table_privilege() (and relkind IN ('r', 'v')) for INSERT, UPDATE,
and DELETE cases. Query_for_list_of_writeables might still require your
patch, though.
--
Itagaki Takahiro
--
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] Tab completion for view triggers in psql
On Tue, Oct 26, 2010 at 10:30:49AM +0900, Itagaki Takahiro wrote: > On Tue, Oct 26, 2010 at 5:01 AM, David Fetter wrote: > > Please find attached patch for $subject :) > > Thank you for maintaining psql tab completion, but I'm not sure > whether tgtype is the best column for the purpose. How about > has_table_privilege() to filter candidate relations in > Query_for_list_of_insertables/deleteables/updateables? That's orthogonal to tgtype, as far as I can tell. The tgtype test is to tell whether it's possible for anyone to do the operation on the view, where has_table_privilege, as I understand it, tells whether some particular role that privilege. Shall I send a new patch with that added? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] security hook on authorization
On Mon, Oct 25, 2010 at 8:51 AM, KaiGai Kohei wrote: > One possible candidate is CheckMyDatabase() that checks ACL_CONNECT > permission for the required database prior to execution of all the > queries. > Currently, we don't have any security hook around here. > But, if we have "InvokeSecurityHook()" here, it will be able to > kill two birds with one stone. The 1st bird is getpeercon(3), and > the 2nd bird is permission check on the selected database. Yeah, that might be a possibility. >> This is closer to the mark, but mostly speculative, and not detailed >> enough to determine whether the proposed hook is properly located. It >> seems rather early to me: this is before we've sent the authentication >> packet to the client, so we couldn't, for example, log the success or >> failure of the authentication; we don't know whether it will succeed >> or fail. >> > Hmm. But the auth_failed() raises a fatal error, so we need to put > a hook before the invocation to log a case of authentication failed. > > | + if (ClientAuthentication_hook) > | + (*ClientAuthentication_hook)(port, status); > | + > | if (status == STATUS_OK) > | sendAuthRequest(port, AUTH_REQ_OK); > | else > | auth_failed(port, status); > > Or, perhaps, we should modify this if-block to ensure the hook being > called after sendAuthRequest() but before auth_failed(). Oh. You know, I am realizing that I misread this patch. This hook is actually after authentication has been done; it's merely before we've told the client what happened. So maybe this is committable as-is, modulo some work on the comments. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Composite Types and Function Parameters
On 10/25/2010 07:12 PM, Tom Lane wrote: However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept "record" parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. That seems like a good idea. I'll look at it for plperl. 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: [HACKERS] xlog.c: WALInsertLock vs. WALWriteLock
On Fri, Oct 22, 2010 at 3:08 PM, fazool mein wrote: > I'm writing a function that will read data from the buffer in xlog (i.e. > from XLogCtl->pages and XLogCtl->xlblocks). I want to make sure that I am > doing it correctly. > For reading from the buffer, do I need to lock WALInsertLock or > WALWriteLock? Also, can you explain a bit the usage of 'LW_SHARED'. Can we > use it for read purposes? Holding WALInsertLock in shared mode prevents other processes from inserting WAL, or in other words it keeps the "end" position from moving, while holding WALWriteLock in shared mode prevents other processes from writing the WAL from the buffers out to the operating system, or in other words it keeps the "start" position from moving. So you could probably take WALInsertLock in shared mode, figure out the current end of WAL position, release the lock; then take WALWriteLock in shared mode, read any WAL before the end of WAL position, and release the lock. But note that this wouldn't guarantee that you read all WAL as it's generated -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Tab completion for view triggers in psql
On Tue, Oct 26, 2010 at 5:01 AM, David Fetter wrote: > Please find attached patch for $subject :) Thank you for maintaining psql tab completion, but I'm not sure whether tgtype is the best column for the purpose. How about has_table_privilege() to filter candidate relations in Query_for_list_of_insertables/deleteables/updateables? -- Itagaki Takahiro -- 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] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 8:13 PM, Jeff Davis wrote: > On Mon, 2010-10-25 at 18:03 -0400, Robert Haas wrote: >> Hmm. Do you have some concrete examples of cases where a range type >> might want to do some representational optimization? > > Let's say for instance you want to keep an timestamp range in 16 bytes. > You could have an 8-byte timestamp, a 7-byte integer that represents the > offset from that timestamp in microseconds, and one byte for flags (e.g. > NULL or infinite boundaries, etc.). I'm not sure that you can make that > representation work in a generic way. See, that gets complicated, because now you're restricting the range of values that can be expressed by the range type to something less than the natural range of the data type. I am not sure the value of supporting that is sufficient to justify the amount of extra code that will be required to make it work. I'd say for a first version, nail down the representation. Perhaps in a future version you could have compress/uncompress methods sort of like GIST, but for a first cut it seems highly desirable to be able to say something like: CREATE TYPE int_range AS RANGE (BASETYPE = int); I hear you complaining that we don't know the values you've called dtype, cmpfunc, addfunc, and subfunc. It seems pretty reasonable to extract cmpfunc, if unspecified, from the default btree opclass for the data type. For the rest, I'm inclined to propose that we support something like: ALTER TYPE timestamptz ADD INTERFACE increment timestamp_pl_interval(timestamptz, interval), ADD INTERFACE decrement timestamp_mi_interval(timestamptz, interval); or ALTER TYPE integer ADD INTERFACE increment int4pl (integer, integer), ADD INTERFACE decrement int4mi (integer, integer), ADD VALUE addative_unit 1::integer, ADD VALUE addative_identity 0::integer; IIRC, Window functions need this information too, so there's value in associating it with the base type, even if we want to allow users to override it when creating ranges. > It's not critical, and perhaps not even desirable. But it crossed my > mind because alignment might make a 17-byte type look like 24 bytes, > which seems pretty wasteful to me. There's no requirement that you set typalign='d'; it's just that if you don't the entries may not be aligned. But it may be that that's a small price to pay for shrinking the on-disk footprint. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Composite Types and Function Parameters
On Oct 25, 2010, at 4:12 PM, Tom Lane wrote: > However, that objection doesn't hold for plperl or pltcl (and likely > not plpython, though I don't know that language enough to be sure). > So it would be a reasonable feature request to teach those PLs to > accept "record" parameters. I think the fact that they don't stems > mostly from nobody having revisited their design since the > infrastructure that supports record_out was created. +1 # Would love to see that. David -- 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] foreign keys for array/period contains relationships
On Mon, Oct 25, 2010 at 5:24 PM, Jeff Davis wrote: > I think that's easier when the PK must contain the FK, because then you > only need to lock one record. Even when you need to lock multiple > records, it seems feasible, and is just an index lookup, right? Do you > see a particular problem? Well if you lock multiple records then it's not clear what operations you should conflict with. Removing any one of them wouldn't actually invalidate the foreign key reference unless you remove the last one. I always assumed this was why we require the unique constraint at all. Otherwise we could just do a sequential scan and lock all the matching records. but we would be preventing someone from removing those records even though removing just one wouldn't be breaking the constraint. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 8:01 PM, Jeff Davis wrote: > On Mon, 2010-10-25 at 18:28 -0400, Robert Haas wrote: >> Oh, maybe I'm confused. Are you saying you'd need multiple copies of >> the base type, or multiple range types based on a single base type? > > The latter. That is, if you want a timestamp range with granularity 1 > second, and a timestamp range with granularity 1 minute, I think those > need to have their own entries in pg_type. OK, I agree with that. Sorry. > The way I look at it, typmod just doesn't help at all. It's useful > perhaps for constraining what a column can hold (like a different kind > of CHECK constraint), or perhaps for display purposes. But typmod isn't > really a part of the type system itself. I view that as a problem in need of fixing, but that's another discussion. > There may be some utility in a pseudo-type like "anyrange", but I think > that's a separate issue. Yeah, interesting idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] foreign keys for array/period contains relationships
On Mon, 2010-10-25 at 22:11 +0300, Peter Eisentraut wrote: > Currently, foreign keys only work with the = operator (the name might be > different, but it needs to behave like equality). I'm thinking there > are other scenarios that could be useful, for example with arrays and > range types. I agree completely. I had not previously considered that arrays could benefit from this idea as well as range types. Mentally, I had already been calling them "foreign range keys" ;) > Implementing the foreign key side of this merely requires the system to > have some knowledge of the required "contains" operator, which it does > in the array case, and something can surely be arranged for the range > case. The problem is you can't do cascading updates or deletes, but you > could do on update/delete restrict, which is still useful. Why can't you do cascading updates/deletes? > Is this sort of thing feasible? Has anyone done more research into the > necessary details? Yes, I think so. #3 and #4 are very feasible. #1 and #2 are, as well, unless I'm missing something. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign keys for array/period contains relationships
On Mon, 2010-10-25 at 12:34 -0700, Greg Stark wrote: > On Mon, Oct 25, 2010 at 12:11 PM, Peter Eisentraut wrote: > > Is this sort of thing feasible? Has anyone done more research into the > > necessary details? > > I think the problems arise when you try to figure out what records you > need to lock to prevent someone from deleting the referenced rows > before you commit. I think that's easier when the PK must contain the FK, because then you only need to lock one record. Even when you need to lock multiple records, it seems feasible, and is just an index lookup, right? Do you see a particular problem? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, discrete and/or continuous
On Mon, 2010-10-25 at 18:03 -0400, Robert Haas wrote: > Hmm. Do you have some concrete examples of cases where a range type > might want to do some representational optimization? Let's say for instance you want to keep an timestamp range in 16 bytes. You could have an 8-byte timestamp, a 7-byte integer that represents the offset from that timestamp in microseconds, and one byte for flags (e.g. NULL or infinite boundaries, etc.). I'm not sure that you can make that representation work in a generic way. It's not critical, and perhaps not even desirable. But it crossed my mind because alignment might make a 17-byte type look like 24 bytes, which seems pretty wasteful to me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, discrete and/or continuous
On Mon, 2010-10-25 at 18:28 -0400, Robert Haas wrote: > Oh, maybe I'm confused. Are you saying you'd need multiple copies of > the base type, or multiple range types based on a single base type? The latter. That is, if you want a timestamp range with granularity 1 second, and a timestamp range with granularity 1 minute, I think those need to have their own entries in pg_type. The way I look at it, typmod just doesn't help at all. It's useful perhaps for constraining what a column can hold (like a different kind of CHECK constraint), or perhaps for display purposes. But typmod isn't really a part of the type system itself. There may be some utility in a pseudo-type like "anyrange", but I think that's a separate issue. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
Merlin Moncure writes: > probably hstore would be more appropriate for something like that. An array is certainly completely the wrong thing if you don't intend all the items to be the same datatype... > You can also declare functions taking composite arrays, anyarray, > variadic array, and variadic "any", although the latter requires > function implementation in C to get the most out of it. If you're willing to write in C you can already create functions that accept type "record" (see record_out for an existence proof/coding example). Making plpgsql do that would be problematic though: it's not so much the record parameter itself, as that you'd be excessively restricted in what you can do with it. If the column names and datatypes aren't pretty predictable, plpgsql isn't going to be the language you want to work in. However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept "record" parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. 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] Composite Types and Function Parameters
On Mon, Oct 25, 2010 at 6:38 PM, Greg wrote: > > Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or > a very ugly looking solution for passing the params then. > > To Postgre dev. team: If anyone who involved in Postgre development reading > this, just a feature suggestion: allow array that can accept combination of > any data types to be passed to a function, for example: > // declare > create function TEST ( anytypearray[] ) ... > // calling > perform TEST (array[bool, int, etc.] ) > This would make such a nice adition to the development for postgre. Although > this may be complecated to achieve. probably hstore would be more appropriate for something like that. You can also declare functions taking composite arrays, anyarray, variadic array, and variadic "any", although the latter requires function implementation in C to get the most out of it. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug: citext not working in non-public schema
Anders Steinlein writes: > http://archives.postgresql.org/pgsql-bugs/2010-03/msg00058.php > This bit us hard (on PostgreSQL 8.4.4). We have a custom domain for > email addresses based on citext, placed in the public schema, while each > user of our application has their own private schemas. The search path > is set to their private schemas, and the few queries which required > explicit access to the type prefixes the type with the public schema, > i.e. WHERE 't...@example.com'::public.email = email_column. Put the citext stuff in a schema that *is* in everyone's search path. It doesn't have to be "public", but it does have to be visible. > Any possibility of getting this fixed? Obliviously I would prefer citext > to work as advertised across schemas. If not, an out-right error thrown > would be much better and consistent than the current situation. The only way to have an error be thrown would be if you remove the implicit cast from citext to text ... which is going to be a far worse notational pain in the rear than fixing your search_path. 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] Composite Types and Function Parameters
Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a
very ugly looking solution for passing the params then.
To Postgre dev. team: If anyone who involved in Postgre development reading
this, just a feature suggestion: allow array that can accept combination of any
data types to be passed to a function, for example:
// declare
create function TEST ( anytypearray[] ) ...
// calling
perform TEST (array[bool, int, etc.] )
This would make such a nice adition to the development for postgre. Although
this may be complecated to achieve.
Thanks!
From: Pavel Stehule
To: Greg
Cc: pgsql-hackers@postgresql.org
Sent: Mon, 25 October, 2010 17:46:47
Subject: Re: [HACKERS] Composite Types and Function Parameters
Hello
I am thinking, so it isn't possible. There are a general datatype anyelement,
but it cannot accept a second general type record.
CREATE TYPE p AS (a text, b int, c bool);
CREATE OR REPLACE FUNCTION fp(p)
RETURNS int AS $$
BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b;
END;
$$ LANGUAGE plpgsql;
postgres=# select fp(row('hello',10, false));
NOTICE: a = hello
fp
10
(1 row)
Regards
Pavel Stehule
Time: 0.814 ms
postgres=# select fp(row('hello',10, false));
2010/10/25 Greg
Hi guys, got across an interesting problem of passing params to a function in
postgre: is it possible to pass a composite parameter to a function without
declaring a type first?
>
>For example:
>
>// declare a function
>create function TEST ( object??? )
>
> object???.paramName// using parameter
>
>
>// calling
>perform TEST( ROW(string, int, bool, etc...) )
>
>Or do I have to do the type declaration for that parameter?
>
>Thanks!
>
>
Re: [HACKERS] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 2:44 PM, Jeff Davis wrote: > On Mon, 2010-10-25 at 14:11 -0400, Robert Haas wrote: >> On Mon, Oct 25, 2010 at 2:01 PM, Jeff Davis wrote: >> > On Mon, 2010-10-25 at 12:20 -0500, Kevin Grittner wrote: >> >> It would be very useful to be able to specify a granularity -- for >> >> example timestamps with a five minute granularity would be useful >> >> for scheduling appointments. In some cases the granularity might be >> >> inferred -- if we have a domain defined as numeric(13,2), it would >> >> be nice if the default granularity was 0.01::numeric. >> > >> > I don't think typmod really helps us much. It's more a property of the >> > column than the type. >> > >> > To specify different granularities, I don't think we can avoid >> > specifying new types with their own entries in pg_type. >> >> Why? > > Because typmod doesn't survive through a function call. Even if it did, > I don't think typmod has a real answer for type promotion, implicit > casting etc. > > If we lose the typmod (and therefore the granularity), then a function > like "adjacent" is difficult to answer if we use a closed-closed > canonical representation (as you suggested); and if we use a closed-open > representation then it's difficult to answer a question like whether a > range contains a specific timestamp. > > Can I turn the question around and ask how you intend to make it work > without new entries in pg_type? Oh, maybe I'm confused. Are you saying you'd need multiple copies of the base type, or multiple range types based on a single base type? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug: citext not working in non-public schema
Researching a nasty bug discovered in our application led me to this bug report and its follow-ups: http://archives.postgresql.org/pgsql-bugs/2010-03/msg00058.php This bit us hard (on PostgreSQL 8.4.4). We have a custom domain for email addresses based on citext, placed in the public schema, while each user of our application has their own private schemas. The search path is set to their private schemas, and the few queries which required explicit access to the type prefixes the type with the public schema, i.e. WHERE 't...@example.com'::public.email = email_column. This *seems* to work, in that no error or warning is thrown, but comparisons are simply silently wrong. This led to a nasty bug in our application as it broke our expectation that the database should enforce this case-insensitive checks. Any possibility of getting this fixed? Obliviously I would prefer citext to work as advertised across schemas. If not, an out-right error thrown would be much better and consistent than the current situation. Regards, -- anders -- 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] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 2:27 PM, Jeff Davis wrote: > On Mon, 2010-10-25 at 13:00 -0400, Robert Haas wrote: >> I'm still confused. It seems to me (and maybe I'm full of it) that >> the distinction between continuous ranges and discrete ranges is >> pretty minor. Suppose you have continuous ranges done, and working. >> The only thing you need to add for discrete ranges (I think) is a >> canonicalization function that converts a range with one or both ends >> open to a range with both ends closed. Then you just apply this >> canonicalization functions to every value supplied by the user before >> doing anything else with it. Poof, discrete ranges! What am I >> missing? > > That's not too far from what I'm suggesting. On the wiki page, under > "approach 2" you'll see that one of the functions needed is a > "constructor" which would put it into a canonical form (if applicable) > and construct the representation. > > I think the difference is that I assumed that the UDFs used for the type > definition would handle both canonicalization and representation. I > think what you're suggesting is that postgres could handle > representation, and just always call the UDF to put it in canonical form > first. That might make it easier to define new types, but might limit > any representation optimizations that certain range types may be able to > exploit. Either approach seems reasonable to me. Hmm. Do you have some concrete examples of cases where a range type might want to do some representational optimization? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Floating-point timestamps versus Range Types
On Mon, Oct 25, 2010 at 4:35 PM, James Cloos wrote: >> "TL" == Tom Lane writes: > > JC> That said, the possiblity of hex i/o format for the float datatypes > JC> would be welcome. > > TL> It's unportable, for two different reasons: > > TL> 2. The printf specifiers you want us to rely on are not standard. > > They are in C99. > > TL> 1. pg_dump output would become platform-specific. This is highly > TL> undesirable. > > It is true that pg would have to test for them in configure and supply > alternative code wherever libc fails to support them. > > I can readily accept that there are many more pressing needs. > > But would such a patch for master be rejected? Let me back up a few steps and ask why you want this in the first place. If there's a real problem here, we should solve it, either in the way you've proposed or in some other manner. But you haven't really said what problem you're trying to solve - just that it would be "welcome", which leaves much to my (not very good) imagination. This is quite a bit OT for this thread so if you want to pursue this I'd suggest starting a new thread in which you start by laying out your case for doing this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ask for review of MERGE
On Mon, Oct 25, 2010 at 4:10 PM, Greg Stark wrote: > On Mon, Oct 25, 2010 at 12:40 PM, Robert Haas wrote: >> Now, as Greg says, that might be what some people want, but it's >> certainly monumentally unserializable. > > To be clear when I said it's what people want what I meant was that in > the common cases it's doing exactly what people want. As opposed to > getting closer to what people want in general but not quite hitting > the mark in the common cases. > > Just as an example I think it's important that in the simplest case, > upsert of a single record, it be 100% guaranteed to do the naive > upsert. If two users are doing the merge of a single key at the same > time one of them had better insert and one of them had better update > or else users are going to be monumentally surprised. Hmm, so let's think about that case. The first merge comes along and finds no match so it fires the NOT MATCHED rule, which inserts a tuple. The second merge comes along and finds no match, so it also fires the NOT MATCHED rule and tries to insert a tuple. But upon consulting the PRIMARY KEY index it finds that an in-doubt tuple exists so it goes to sleep waiting for the first transaction to commit or abort. If the first transaction commits it then decides that the jig is up and fails. We could (maybe) fix this by doing something similar to what EPQ does for updates: when the first transaction commits, instead of redoing the insert, we back up and recheck whether the new tuple would have matched the join clause and, if so, we instead fire the MATCHED action on the updated tuple. If not, we fire NOT MATCHED anyway. I'm not sure how hard that would be, or whether it would introduce any other nasty anomalies in more complex cases. Alternatively, we could introduce an UPSERT or REPLACE statement intended to handle exactly this case and leave MERGE for more complex situations. It's pretty easy to imagine what the coding of that should look like: if we encounter an in-doubt tuple in we wait on its xmin. If the transaction aborts, we insert. If it commits, and we're in READ COMMITTED mode, we update it; but if we're in REPEATABLE READ or SERIALIZABLE mode, we abort with a serialization error. That's a lot simpler to understand and reason about than MERGE in its full generality. I think it's pretty much hopeless to think that MERGE is going to work in complex concurrent scenarios without creating serialization anomalies, or at least rollbacks. I think that's baked into the nature of what the statement does. To simulate MERGE, you need to read from the target table and then do writes that depend on what you read. If you do that with the commands that are available today, you're going to get serialization anomalies and/or rollbacks under concurrency. The mere fact of that logic being inside the database rather than outside isn't going to make that go away. Now sometimes, as with exclusion constraints, you can play games with dirty snapshots to get the semantics you want, but whether that's possible in a particular case depends on the details of the operation being performed, and here I think it can't be done. Some operations are *fundamentally* unserializable. A very simple example of this is a sequence that is guaranteed not to have gaps (a feature we've occasionally been requested to provide). If N processes request a sequence number simultaneously, you have to hand out a value to the first guy and wait and see whether he commits or aborts before deciding what number to give the second guy. That sucks, so usually we just design our applications not to require that sequences be gap-free. Similarly here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Floating-point timestamps versus Range Types
> "TL" == Tom Lane writes: JC> That said, the possiblity of hex i/o format for the float datatypes JC> would be welcome. TL> It's unportable, for two different reasons: TL> 2. The printf specifiers you want us to rely on are not standard. They are in C99. TL> 1. pg_dump output would become platform-specific. This is highly TL> undesirable. It is true that pg would have to test for them in configure and supply alternative code wherever libc fails to support them. I can readily accept that there are many more pressing needs. But would such a patch for master be rejected? -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] add label to enum syntax
Andrew Dunstan writes: > LABEL is already an unreserved keyword, and I'm pretty sure that's all > we'll need. The only reason it's a keyword is the SECURITY LABEL patch that went in a month or so ago; which is syntax that might still be thought better of before it gets to a release. But I seem to be in the minority, so I'll shut up now. 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] Extensions, this time with a patch
Excerpts from Dimitri Fontaine's message of vie oct 22 16:43:56 -0300 2010: > Dimitri Fontaine writes: > > For information, when we talk about performance problem, please note > > that on my workstation with a default setup (not that it's important > > here) we're talking about 86,420 ms for a loop of 100 > > perform * from pg_extensions; BTW it strikes me that it would be easier on the code that there were just a couple of simple functions, one returning the list of installed extensions and another one returning the list of installable extensions. The rest of SRF functions needn't be implemented in C, you could implement them in SQL instead by joining to pg_depend and whatnot. Also, PFA a couple of minor fixes. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support 0001-A-bunch-of-minor-fixes.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ask for review of MERGE
Robert Haas wrote: > Kevin Grittner wrote: >> I would have thought that the INSERT would have >> created an "in doubt" tuple which would block the UPDATE. > This is just standard MVCC - readers don't block writers, nor > writers readers. Sure, but I tend to think of both INSERT and UPDATE as writes. ;-) > You might also think about what would happen if the UPDATE > were run before the INSERT > either concurrent case is equivalent to the serial > schedule where the update precedes the insert. I guess that's persuasive enough. It feels funny, but the argument looks sound, so I guess it's just a case of my intuition being faulty. > In the case of a MERGE that matches a just-inserted invisible > tuple but no visible tuple, things are a bit stickier. Well, more generally it can lead to anomalies in a more complex combination of actions, since it creates, as you imply above, a rw-dependency from the transaction doing the UPDATE to the transaction doing the INSERT, so the combination can form part of a cycle in apparent order of execution which can produce an anomaly. The more I look at it, the more clear it is that current behavior is correct and what the implications are. I've just missed that detail until now, wrongly assuming that it would be a write conflict. > [example of MERGE which can not serialize with a concurrent > transaction, and possible outcomes if there is no serialization > failure] > Now, as Greg says, that might be what some people want, but it's > certainly monumentally unserializable. Yeah. MERGE should probably be sensitive to the transaction isolation level, at least to the extent that MERGE in a SERIALIZABLE transaction plays nice with other SERIALIZABLE transactions. That would be necessary to allow business rules enforced through triggers to be able to guarantee data integrity. It would mean that a MERGE involving tables which were the target of modifications from concurrent SERIALIZABLE transactions would be likely to fail and/or to cause other transactions to fail. -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] add label to enum syntax
On 10/25/2010 04:03 PM, Pavel Stehule wrote: 2010/10/25 Andrew Dunstan: On 10/25/2010 02:51 PM, Robert Haas wrote: Personally, I prefer LABEL. But I could live with VALUE. That's roughly my position. It would be consistent with the name we use in the catalogs, as well as what's in the docs. I don't think it's as opaque as Tom seems to suggest. An enum is pretty much an ordered set of labels. But I could certainly live with VALUE if that's the consensus. I agree with you. There are some better keywords than VALUE - maybe ELEMENT or just LABEL. I understand if there must be a reserved keyword - but if not I prefer LABEL too. LABEL is already an unreserved keyword, and I'm pretty sure that's all we'll need. 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: [HACKERS] ask for review of MERGE
On Mon, Oct 25, 2010 at 12:40 PM, Robert Haas wrote: > Now, as Greg says, that might be what some people want, but it's > certainly monumentally unserializable. To be clear when I said it's what people want what I meant was that in the common cases it's doing exactly what people want. As opposed to getting closer to what people want in general but not quite hitting the mark in the common cases. Just as an example I think it's important that in the simplest case, upsert of a single record, it be 100% guaranteed to do the naive upsert. If two users are doing the merge of a single key at the same time one of them had better insert and one of them had better update or else users are going to be monumentally surprised. I guess I hadn't considered all the cases and I agree it's important that our behaviour make some kind of sense and be consistent with how we handle updates and of existing in-doubt tuples. I wasn't trying to introduce a whole new mode of operation, just work from analogy from the way update works. It's clear that even with our existing semantics there are strange corner cases once you get to multiple updates happening in a single transaction. But we get the simple cases right and even in the more complex cases, while it's not truly serializable we should be able to come up with some basic smell tests that we pass. My understanding is that currently we generally treat DML in one of two ways depending on whether it's returning data to the user or updating data in the table (include select for share). If it's returning data to the user we use a snapshot to give the user a consistent view of the database. If it's altering data in the database we use the snapshot to get a consistent set of records and then apply the updates to the most recent version. The anomaly you showed with update and the problem with MERGE are both because the operation was simultaneously doing a "read" -- the WHERE clause and the uniqueness check in the MERGE -- and a write. This is already the kind of case where we do weird things -- what kind of behaviour would be consistent with our existing, somewhat weird, behaviour? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign keys for array/period contains relationships
2010/10/25 Robert Haas : > On Mon, Oct 25, 2010 at 3:11 PM, Peter Eisentraut wrote: >> Currently, foreign keys only work with the = operator (the name might be >> different, but it needs to behave like equality). I'm thinking there >> are other scenarios that could be useful, for example with arrays and >> range types. >> >> Example #1: Foreign key side is an array, every member must match some >> PK. >> >> CREATE TABLE pk (a int PRIMARKY KEY, ...); >> >> CREATE TABLE fk (x int[] REFERENCES pk (a), ...); What about optimalizations and planning? This is classic sample how don't use a arrays? Regards Pavel > > I've wished for this before when doing app dev with PG. I think it > would be pretty neat. The other cases seem potentially useful, too, > but especially this one. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Floating-point timestamps versus Range Types
James Cloos writes: > That said, the possiblity of hex i/o format for the float datatypes > would be welcome. It's unportable, for two different reasons: 1. pg_dump output would become platform-specific. This is highly undesirable. 2. The printf specifiers you want us to rely on are not standard. Cf Single Unix Spec, http://www.opengroup.org/onlinepubs/007908799/xsh/fprintf.html which does not define either %A or %a. 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] add label to enum syntax
2010/10/25 Andrew Dunstan : > > > On 10/25/2010 02:51 PM, Robert Haas wrote: >> >> On Mon, Oct 25, 2010 at 2:41 PM, Tom Lane wrote: >>> >>> "David E. Wheeler" writes: On Oct 25, 2010, at 10:08 AM, Tom Lane wrote: > > I can see the point of that, but I don't find LABEL to be a > particularly > great name for the elements of an enum type, and so I'm not in favor of > institutionalizing that name in the syntax. How about ADD VALUE? So the docs have called them "labels" for quite some time. >>> >>> There are some places in the docs that use that term, but there are >>> others that don't. In any case, using the term in the SQL syntax >>> casts it in stone, not silly putty ... >> >> Personally, I prefer LABEL. But I could live with VALUE. > > That's roughly my position. It would be consistent with the name we use in > the catalogs, as well as what's in the docs. I don't think it's as opaque as > Tom seems to suggest. An enum is pretty much an ordered set of labels. But I > could certainly live with VALUE if that's the consensus. I agree with you. There are some better keywords than VALUE - maybe ELEMENT or just LABEL. I understand if there must be a reserved keyword - but if not I prefer LABEL too. Regards Pavel > > 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tab completion for view triggers in psql
Folks,
Please find attached patch for $subject :)
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***
*** 303,308 static const SchemaQuery Query_for_list_of_tables = {
--- 303,375
NULL
};
+ /* The bit masks for the following three functions come from
+ * src/include/catalog/pg_trigger.h.
+ */
+ static const SchemaQuery Query_for_list_of_insertables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind = 'r' OR (c.relkind = 'v' AND c.relhastriggers AND EXISTS "
+ "(SELECT 1 FROM pg_catalog.pg_trigger t WHERE t.tgrelid = c.oid AND
t.tgtype | (1 << 2) = t.tgtype))",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
+ static const SchemaQuery Query_for_list_of_deleteables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind = 'r' OR (c.relkind = 'v' AND c.relhastriggers AND EXISTS "
+ "(SELECT 1 FROM pg_catalog.pg_trigger t WHERE t.tgrelid = c.oid AND
t.tgtype | (1 << 3) = t.tgtype))",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
+ static const SchemaQuery Query_for_list_of_updateables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind = 'r' OR (c.relkind = 'v' AND c.relhastriggers AND EXISTS "
+ "(SELECT 1 FROM pg_catalog.pg_trigger t WHERE t.tgrelid = c.oid AND
t.tgtype | (1 << 4) = t.tgtype))",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
+ static const SchemaQuery Query_for_list_of_writeables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind = 'r' OR (c.relkind = 'v' AND c.relhastriggers AND EXISTS "
+ "(SELECT 1 FROM pg_catalog.pg_trigger t WHERE t.tgrelid = c.oid AND
(t.tgtype & ( (1<<2) | (1<<3) | (1<<4)))::bool)",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
static const SchemaQuery Query_for_list_of_tisv = {
/* catname */
"pg_catalog.pg_class c",
***
*** 333,338 static const SchemaQuery Query_for_list_of_tsv = {
--- 400,420
NULL
};
+ static const SchemaQuery Query_for_list_of_tv = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind IN ('r', 'v')",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+ };
+
static const SchemaQuery Query_for_list_of_views = {
/* catname */
"pg_catalog.pg_class c",
***
*** 630,636 psql_completion(char *text, int start, int end)
*prev2_wd,
*prev3_wd,
*prev4_wd,
! *prev5_wd;
static const char *const sql_commands[] = {
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE",
"CLUSTER",
--- 712,719
*prev2_wd,
*prev3_wd,
*prev4_wd,
! *prev5_wd,
! *prev6_wd;
static const char *const sql_commands[] = {
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE",
"CLUSTER",
***
*** 669,675 psql_completion(char *text, int start, int end)
completion_info_charp2 = NULL;
/*
!* Scan the input line before our current position for the last five
* words. According to those we'll make some smart decisions on what the
* user is probably intending to type. TODO: Use strtokx() to do this.
*/
--- 752,758
completion_info_charp2 = NULL;
/*
!* Scan the input line before our current position for the last six
* words. Acco
Re: [HACKERS] Floating-point timestamps versus Range Types
> "JD" == Jeff Davis writes: JD> 2. Fix the input/output functions in a special mode for dump/reload, JD> to make them true inverses. JC> That can be done by supporting the %A printf(3)/scanf(3) format. JD> I don't happen to see a %A format in the man page, but I doubt the JD> output would look like a timestamp. Modern printf(1) also supports it, so an easy example: :; printf '%A\n' 3.1415926535897932384626433832795029 0XC.90FDAA22168C235P-2 %a is the same, but with miniscule letters. It is the hex format of the float types, and defaults to exactly enough precision. The length modifier L makes %a expect a long double. JD> And if it doesn't look like a timestamp, it violates the spirit of a JD> logical dump of the data. Point taken. Had I read the whole thread before replying I would have been reminded that the float timestamps were archaic; that avoids any need of %A for timestamps. That said, the possiblity of hex i/o format for the float datatypes would be welcome. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] add label to enum syntax
Andrew Dunstan wrote: >> Personally, I prefer LABEL. But I could live with VALUE. > > That's roughly my position. LABEL would seem more natural to me. I would tend to think of the VALUE as the hidden number which determines the order. -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] add label to enum syntax
On 10/25/2010 02:51 PM, Robert Haas wrote: On Mon, Oct 25, 2010 at 2:41 PM, Tom Lane wrote: "David E. Wheeler" writes: On Oct 25, 2010, at 10:08 AM, Tom Lane wrote: I can see the point of that, but I don't find LABEL to be a particularly great name for the elements of an enum type, and so I'm not in favor of institutionalizing that name in the syntax. How about ADD VALUE? So the docs have called them "labels" for quite some time. There are some places in the docs that use that term, but there are others that don't. In any case, using the term in the SQL syntax casts it in stone, not silly putty ... Personally, I prefer LABEL. But I could live with VALUE. That's roughly my position. It would be consistent with the name we use in the catalogs, as well as what's in the docs. I don't think it's as opaque as Tom seems to suggest. An enum is pretty much an ordered set of labels. But I could certainly live with VALUE if that's the consensus. 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: [HACKERS] foreign keys for array/period contains relationships
On Mon, Oct 25, 2010 at 3:11 PM, Peter Eisentraut wrote: > Currently, foreign keys only work with the = operator (the name might be > different, but it needs to behave like equality). I'm thinking there > are other scenarios that could be useful, for example with arrays and > range types. > > Example #1: Foreign key side is an array, every member must match some > PK. > > CREATE TABLE pk (a int PRIMARKY KEY, ...); > > CREATE TABLE fk (x int[] REFERENCES pk (a), ...); I've wished for this before when doing app dev with PG. I think it would be pretty neat. The other cases seem potentially useful, too, but especially this one. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ask for review of MERGE
On Mon, Oct 25, 2010 at 3:15 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> rhaas=# create table concurrent (x integer primary key); >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index >> "concurrent_pkey" for table "concurrent" >> CREATE TABLE >> rhaas=# insert into x values (1); >> rhaas=# begin; >> BEGIN >> rhaas=# insert into concurrent values (2); >> INSERT 0 1 >> >> >> >> rhaas=# update concurrent set x=x where x=2; >> UPDATE 0 > > That surprised me. I would have thought that the INSERT would have > created an "in doubt" tuple which would block the UPDATE. What is > the reason for not doing so? This is just standard MVCC - readers don't block writers, nor writers readers. You might also think about what would happen if the UPDATE were run before the INSERT of (2). There's no serialization anomaly here, because either concurrent case is equivalent to the serial schedule where the update precedes the insert. In the case of a MERGE that matches a just-inserted invisible tuple but no visible tuple, things are a bit stickier. Let's suppose we're trying to use MERGE to get UPSERT semantics. If the MERGE command has the obvious behavior of ignoring the invisible tuple just as UPDATE or DELETE would do, then clearly any equivalent serial schedule must run the MERGE before the INSERT (because if it were run after the INSERT, it would fire the MATCHED action rather than the NOT MATCHED action). But if the merge were run before the INSERT, then the INSERT would have failed with a unique key violation; instead, the merge fails with a unique key violation. On the other hand, if the MERGE sees the invisible tuple, essentially using SnapshotNow semantics, as Greg Stark proposed, you get a different (and probably worse) class of serialization anomalies. For example, suppose the table has rows 1-100 and you do an update adding 1000 to each value concurrently with merging in the values 51-100. You might get something like this: - MERGE scans rows 1-75, firing MATCHED for rows 51-75. - UPDATE commits. - MERGE scans rows 76-100, firing NOT MATCHED for each. Now, as Greg says, that might be what some people want, but it's certainly monumentally unserializable. In a serial execution schedule, the MERGE will either run before the UPDATE, in which case MATCHED will fire for rows 51-100, or else the UPDATE will run before the MERGE, in which case NOT MATCHED will fire for rows 51-100. No serial schedule is going to fire MATCHED for some rows and NOT MATCHED for others. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] foreign keys for array/period contains relationships
On Mon, Oct 25, 2010 at 12:11 PM, Peter Eisentraut wrote: > Is this sort of thing feasible? Has anyone done more research into the > necessary details? I think the problems arise when you try to figure out what records you need to lock to prevent someone from deleting the referenced rows before you commit. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ask for review of MERGE
Robert Haas wrote: > rhaas=# create table concurrent (x integer primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "concurrent_pkey" for table "concurrent" > CREATE TABLE > rhaas=# insert into x values (1); > rhaas=# begin; > BEGIN > rhaas=# insert into concurrent values (2); > INSERT 0 1 > > > > rhaas=# update concurrent set x=x where x=2; > UPDATE 0 That surprised me. I would have thought that the INSERT would have created an "in doubt" tuple which would block the UPDATE. What is the reason for not doing so? FWIW I did a quick test and REPEATABLE READ also lets this pass but with the SSI patch SERIALIZABLE seems to cover this correctly, generating a serialization failure where such access is involved in write skew: test=# create table concurrent (x integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "concurrent_pkey" for table "concurrent" CREATE TABLE test=# insert into concurrent select generate_series(1, 2); INSERT 0 2 test=# begin isolation level serializable; BEGIN test=# insert into concurrent values (0); INSERT 0 1 test=# update concurrent set x = 30001 where x = 3; UPDATE 0 test=# begin isolation level serializable; BEGIN test=# insert into concurrent values (3); INSERT 0 1 test=# update concurrent set x = -1 where x = 0; UPDATE 0 test=# commit; ERROR: could not serialize access due to read/write dependencies among transactions HINT: The transaction might succeed if retried. I'll need to add a test to cover this, because it might have broken with one of the optimizations on my list, had you not point out this behavior. On the other hand: test=# drop table concurrent ; DROP TABLE test=# create table concurrent (x integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "concurrent_pkey" for table "concurrent" CREATE TABLE test=# insert into concurrent select generate_series(1, 2); INSERT 0 2 test=# begin isolation level serializable; BEGIN test=# insert into concurrent values (0); INSERT 0 1 test=# begin isolation level serializable; BEGIN test=# select * from concurrent where x = 0; x --- (0 rows) test=# insert into concurrent values (0); test=# commit; COMMIT ERROR: duplicate key value violates unique constraint "concurrent_pkey" DETAIL: Key (x)=(0) already exists. Anyway, I thought this might be of interest in terms of the MERGE patch concurrency issues, since the SSI patch has been mentioned. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] foreign keys for array/period contains relationships
Currently, foreign keys only work with the = operator (the name might be different, but it needs to behave like equality). I'm thinking there are other scenarios that could be useful, for example with arrays and range types. Example #1: Foreign key side is an array, every member must match some PK. CREATE TABLE pk (a int PRIMARKY KEY, ...); CREATE TABLE fk (x int[] REFERENCES pk (a), ...); Example #2: Foreign key side as a (hypothetical) period type, PK is a timestamp. Every FK period must contain a PK timestamp. CREATE TABLE pk (a timestamp PRIMARY KEY, ...); CREATE TABLE fk (x period/range of timestamp REFERENCES pk (a), ...); Implementing the foreign key side of this merely requires the system to have some knowledge of the required "contains" operator, which it does in the array case, and something can surely be arranged for the range case. The problem is you can't do cascading updates or deletes, but you could do on update/delete restrict, which is still useful. It get's more interesting when the "container" type is the primary key: Example #3: PK is array, FK is element type. FK must be element of some PK array. CREATE TABLE pk (a int[] PRIMARY KEY, ...); CREATE TABLE fk (x int REFERENCES pk (a), ...); Example #4: PK is period, FK is timestamp. FK must be contained in some PK period. CREATE TABLE pk (a period PRIMARY KEY, ...); CREATE TABLE fk (x timestamp REFERENCES pk (a), ...); As above, we can probably arrange the operator knowledge to make these checks. But I think additionally, you'd need an exclusion constraint on the PK side to ensure nonoverlapping arrays/periods so that on update/delete restrict as well as cascading deletes work. Additional interesting examples involve IP network containment using inet/cidr or ip4/ip4r. There, you'd probably need additional syntax to tell the system explicitly which operators to use. Now I originally arrived at this issue via Example #1, but it appeared to me that with the ongoing work on range types, Example #4 would be a very eminent use case. Is this sort of thing feasible? Has anyone done more research into the necessary details? -- 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] Floating-point timestamps versus Range Types
On Mon, 2010-10-25 at 13:54 -0400, James Cloos wrote: > > "JD" == Jeff Davis writes: > > JD> 2. Fix the input/output functions in a special mode for dump/reload, > JD>to make them true inverses. > > That can be done by supporting the %A printf(3)/scanf(3) format. I don't happen to see a %A format in the man page, but I doubt the output would look like a timestamp. And if it doesn't look like a timestamp, it violates the spirit of a logical dump of the data. We could just base64-encode the binary and print that, but that doesn't maintain the spirit of a logical data dump either. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] add label to enum syntax
On Mon, Oct 25, 2010 at 2:41 PM, Tom Lane wrote: > "David E. Wheeler" writes: >> On Oct 25, 2010, at 10:08 AM, Tom Lane wrote: >>> I can see the point of that, but I don't find LABEL to be a particularly >>> great name for the elements of an enum type, and so I'm not in favor of >>> institutionalizing that name in the syntax. How about ADD VALUE? > >> So the docs have called them "labels" for quite some time. > > There are some places in the docs that use that term, but there are > others that don't. In any case, using the term in the SQL syntax > casts it in stone, not silly putty ... Personally, I prefer LABEL. But I could live with VALUE. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types, discrete and/or continuous
On Mon, 2010-10-25 at 14:11 -0400, Robert Haas wrote: > On Mon, Oct 25, 2010 at 2:01 PM, Jeff Davis wrote: > > On Mon, 2010-10-25 at 12:20 -0500, Kevin Grittner wrote: > >> It would be very useful to be able to specify a granularity -- for > >> example timestamps with a five minute granularity would be useful > >> for scheduling appointments. In some cases the granularity might be > >> inferred -- if we have a domain defined as numeric(13,2), it would > >> be nice if the default granularity was 0.01::numeric. > > > > I don't think typmod really helps us much. It's more a property of the > > column than the type. > > > > To specify different granularities, I don't think we can avoid > > specifying new types with their own entries in pg_type. > > Why? Because typmod doesn't survive through a function call. Even if it did, I don't think typmod has a real answer for type promotion, implicit casting etc. If we lose the typmod (and therefore the granularity), then a function like "adjacent" is difficult to answer if we use a closed-closed canonical representation (as you suggested); and if we use a closed-open representation then it's difficult to answer a question like whether a range contains a specific timestamp. Can I turn the question around and ask how you intend to make it work without new entries in pg_type? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] add label to enum syntax
"David E. Wheeler" writes: > On Oct 25, 2010, at 10:08 AM, Tom Lane wrote: >> I can see the point of that, but I don't find LABEL to be a particularly >> great name for the elements of an enum type, and so I'm not in favor of >> institutionalizing that name in the syntax. How about ADD VALUE? > So the docs have called them "labels" for quite some time. There are some places in the docs that use that term, but there are others that don't. In any case, using the term in the SQL syntax casts it in stone, not silly putty ... 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] Floating-point timestamps versus Range Types
> "JD" == Jeff Davis writes: JD> 2. Fix the input/output functions in a special mode for dump/reload, JD>to make them true inverses. That can be done by supporting the %A printf(3)/scanf(3) format. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] Range Types, discrete and/or continuous
On Mon, 2010-10-25 at 13:00 -0400, Robert Haas wrote: > I'm still confused. It seems to me (and maybe I'm full of it) that > the distinction between continuous ranges and discrete ranges is > pretty minor. Suppose you have continuous ranges done, and working. > The only thing you need to add for discrete ranges (I think) is a > canonicalization function that converts a range with one or both ends > open to a range with both ends closed. Then you just apply this > canonicalization functions to every value supplied by the user before > doing anything else with it. Poof, discrete ranges! What am I > missing? That's not too far from what I'm suggesting. On the wiki page, under "approach 2" you'll see that one of the functions needed is a "constructor" which would put it into a canonical form (if applicable) and construct the representation. I think the difference is that I assumed that the UDFs used for the type definition would handle both canonicalization and representation. I think what you're suggesting is that postgres could handle representation, and just always call the UDF to put it in canonical form first. That might make it easier to define new types, but might limit any representation optimizations that certain range types may be able to exploit. Either approach seems reasonable to me. I know the wiki page isn't very formal about the approaches yet, but as we start to coalesce around a basic idea I'll write it up in more detail. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bug in explain - core dump
Pavel Stehule writes: > DROP TABLE IF EXISTS foo; > CREATE TABLE foo(a int, b int); > INSERT INTO foo SELECT (random()*1)::int, (random()*10)::int from > generate_series(1,10); > ANALYZE foo; > CREATE INDEX ON foo(a,b); > CREATE INDEX ON foo(b,a); > EXPLAIN SELECT max(a), b >FROM foo, generate_series(0,30) g(v) > WHERE a = (SELECT max(a) FROM foo WHERE b = v) > GROUP BY b; Fixed, thanks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 2:01 PM, Jeff Davis wrote: > On Mon, 2010-10-25 at 12:20 -0500, Kevin Grittner wrote: >> It would be very useful to be able to specify a granularity -- for >> example timestamps with a five minute granularity would be useful >> for scheduling appointments. In some cases the granularity might be >> inferred -- if we have a domain defined as numeric(13,2), it would >> be nice if the default granularity was 0.01::numeric. > > I don't think typmod really helps us much. It's more a property of the > column than the type. > > To specify different granularities, I don't think we can avoid > specifying new types with their own entries in pg_type. Why? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ask for review of MERGE
On Mon, Oct 25, 2010 at 1:42 PM, Greg Stark wrote: > On Sun, Oct 24, 2010 at 10:43 AM, Robert Haas wrote: >> But let's back up and talk about MVCC for a minute. Suppose we have >> three source tuples, (1), (2), and (3); and the target table contains >> tuples (1) and (2), of which only (1) is visible to our MVCC snapshot; >> suppose also an equijoin. Clearly, source tuple (1) should fire the >> MATCHED rule and source tuple (3) should fire the NOT MATCHED rule, >> but what in the world should source tuple (2) do? AFAICS, the only >> sensible behavior is to throw a serialization error, because no matter >> what you do the results won't be equivalent to a serial execution of >> the transaction that committed target tuple (2) and the transaction >> that contains the MERGE. > > So the behaviour we get with UPDATE in this situation is that we > update (2) so I would expect this to execute the MATCHED rule. Not exactly. Consider this example: rhaas=# create table concurrent (x integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "concurrent_pkey" for table "concurrent" CREATE TABLE rhaas=# insert into x values (1); rhaas=# begin; BEGIN rhaas=# insert into concurrent values (2); INSERT 0 1 rhaas=# update concurrent set x=x where x=2; UPDATE 0 > The key > distinction is that since we're not returning the data to the user the > user sees we want to update the most recent version and it's "almost" > as if we ran "after" all the other transactions. It's not really > serializable and I think in serializable mode we throw a serialization > failure instead but in most usage patterns it's precisely what the > user wants. I think it would be perfectly reasonable to have a transaction isolation level that does not use a snapshot at all and instead runs everything relative to SnapshotNow, and people could use it with MERGE if they were so inclined. I think this would correspond more or less to the READ COMMITTED isolation level specified in the standard; what we now call READ COMMITTED is actually better than READ COMMITTED but not quite as good as REPEATABLE READ. That, combined with an exclusive lock on the table (or, perhaps, some kind of predicate locking mechanism) would be sufficient to prevent serialization anomalies. However, I don't think that implementing those semantics for just this one command (or part of it) makes a whole lot of sense. The EPQ behavior of our current default isolation level is really pretty strange, and adding a random wart that the target table (but not the source table) in a MERGE query gets scanned using SnapshotNow would be one more piece of strangeness atop the strangeness we already have. And, as we just saw with the enum stuff, SnapshotNow can lead to some awfully strange behavior - you could end up processing half of the data from a concurrent transaction and missing the other half. > Here "bbb" contained two records when we began with values "1" and "2" > but the "2" was inserted in a transaction which hadn't committed yet. > It commited after the update. > > postgres=> begin; > BEGIN > postgres=> select * from bbb; > i > --- > 1 > (1 row) > > postgres=> update bbb set i = i+1; > UPDATE 2 > postgres=> commit; > COMMIT > postgres=> select * from bbb; > i > --- > 2 > 3 > (2 rows) Well, at least on my system, if the transaction inserting (2) hasn't committed yet, that UPDATE statement will block until it does, because trying to change i from 1 to 2 causes the update of the unique index to block, since there's an in-doubt tuple with (2) already. Then it will continue on as you've shown here, due to EPQ. But if you do the same statement with i = i + 10 instead of + 1, then it doesn't block, and only updates the one row that's visible. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ask for review of MERGE
Greg Stark wrote: > Robert Haas wrote: >> But let's back up and talk about MVCC for a minute. Suppose we >> have three source tuples, (1), (2), and (3); and the target table >> contains tuples (1) and (2), of which only (1) is visible to our >> MVCC snapshot; suppose also an equijoin. Clearly, source tuple >> (1) should fire the MATCHED rule and source tuple (3) should fire >> the NOT MATCHED rule, but what in the world should source tuple >> (2) do? AFAICS, the only sensible behavior is to throw a >> serialization error, because no matter what you do the results >> won't be equivalent to a serial execution of the transaction that >> committed target tuple (2) and the transaction that contains the >> MERGE. > > So the behaviour we get with UPDATE in this situation is that we > update (2) so I would expect this to execute the MATCHED rule. Certainly that would be consistent with the behavior of READ COMMITTED -- wait for commit or rollback of the concurrent transaction, and then proceed with whatever data is there after completion of the other transaction. With REPEATABLE READ or SERIALIZABLE you would block until commit of the other transaction and terminate with a write conflict -- a form of serialization failure. If the other transaction rolls back you INSERT. At least, that would be the least surprising behavior to me. -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] add label to enum syntax
On Oct 25, 2010, at 10:08 AM, Tom Lane wrote: > I can see the point of that, but I don't find LABEL to be a particularly > great name for the elements of an enum type, and so I'm not in favor of > institutionalizing that name in the syntax. How about ADD VALUE? From the fine manual: > The second form of CREATE TYPE creates an enumerated (enum) type, as > described in Section 8.7. Enum types take a list of one or more quoted > labels, each of which must be less than NAMEDATALEN bytes long (64 in a > standard PostgreSQL build). So the docs have called them "labels" for quite some time. David -- 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] Range Types, discrete and/or continuous
On Mon, 2010-10-25 at 12:20 -0500, Kevin Grittner wrote: > It would be very useful to be able to specify a granularity -- for > example timestamps with a five minute granularity would be useful > for scheduling appointments. In some cases the granularity might be > inferred -- if we have a domain defined as numeric(13,2), it would > be nice if the default granularity was 0.01::numeric. I don't think typmod really helps us much. It's more a property of the column than the type. To specify different granularities, I don't think we can avoid specifying new types with their own entries in pg_type. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ask for review of MERGE
On Sun, Oct 24, 2010 at 10:43 AM, Robert Haas wrote: > But let's back up and talk about MVCC for a minute. Suppose we have > three source tuples, (1), (2), and (3); and the target table contains > tuples (1) and (2), of which only (1) is visible to our MVCC snapshot; > suppose also an equijoin. Clearly, source tuple (1) should fire the > MATCHED rule and source tuple (3) should fire the NOT MATCHED rule, > but what in the world should source tuple (2) do? AFAICS, the only > sensible behavior is to throw a serialization error, because no matter > what you do the results won't be equivalent to a serial execution of > the transaction that committed target tuple (2) and the transaction > that contains the MERGE. So the behaviour we get with UPDATE in this situation is that we update (2) so I would expect this to execute the MATCHED rule. The key distinction is that since we're not returning the data to the user the user sees we want to update the most recent version and it's "almost" as if we ran "after" all the other transactions. It's not really serializable and I think in serializable mode we throw a serialization failure instead but in most usage patterns it's precisely what the user wants. Here "bbb" contained two records when we began with values "1" and "2" but the "2" was inserted in a transaction which hadn't committed yet. It commited after the update. postgres=> begin; BEGIN postgres=> select * from bbb; i --- 1 (1 row) postgres=> update bbb set i = i+1; UPDATE 2 postgres=> commit; COMMIT postgres=> select * from bbb; i --- 2 3 (2 rows) -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Segfault in 9.0 inlining SRF
Brendan Jurd writes: > This whole business of passing around global pointers while switching > memory contexts seems like an optimal breeding-ground for bugs. Yeah. If it were to get significantly more complicated than this, the best solution IMO would be to give up on trying to use a temporary memory context during function inlining, and just accept that whatever memory we chew up there is going to be leaked for the duration of planning. 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] Range Types, discrete and/or continuous
Jeff Davis wrote: > Last development cycle, one of the questions that was unresolved > was whether to handle ranges like a discrete set (that is, [1,5) = > [1,4] ) or continuous or both. > > I think that discrete ranges are required. For instance, day range > and IP address ranges are two examples where treating them > continuously would clearly cause confusion. "Monday until > Thursday" is the same as "Monday through Wednesday," and it would > be dangerous to treat them as different values. All of the use cases I've been able to imagine as useful for our shop would work fine with discrete ranges. Continuous ranges seem as though they would be more clumsy and dangerous for these use cases. Having not seen or imagined a practical use case for continuous ranges, I'm indifferent to support for them. It would be very useful to be able to specify a granularity -- for example timestamps with a five minute granularity would be useful for scheduling appointments. In some cases the granularity might be inferred -- if we have a domain defined as numeric(13,2), it would be nice if the default granularity was 0.01::numeric. -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] Segfault in 9.0 inlining SRF
On 26 October 2010 03:42, Tom Lane wrote: > Brendan Jurd writes: >> Thanks for the hint; I found that the attached patch resolved my >> specific segfault, but I am wondering whether it goes far enough. > > Well, it definitely doesn't go far enough, because the invalItems list > has to be restored to its original state if we fail to inline at some > point after calling eval_const_expressions. Point taken. > I thought about whether we need something more general, but for the > moment I think this is sufficient; eval_const_expressions has only > very limited reason to examine the PlannerInfo data at all, and less > reason to modify it. Copying the whole structure would be overkill. > Moreover, it wouldn't do anything to improve modularity anyhow: this > function would still have to know which parts of the structure to copy > back to the top level, and which not. So it'd still need to know > quite a bit about exactly what eval_const_expressions is doing. That makes sense to me. This whole business of passing around global pointers while switching memory contexts seems like an optimal breeding-ground for bugs. It would be nice to come up with a more ... well, "global" way to manage PlannerGlobal. To me it suggests something along the lines of a dedicated MemoryContext in which PlannerGlobal and all its members live, and you operate on PlannerGlobal by calling methods, rather than by directly twiddling its pointers. But, that is probably way over the top for this, given its narrow area of effect. Cheers, BJ -- 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] add label to enum syntax
Andrew Dunstan writes: > It occurred to me in the dead of the night that instead of: > ALTER TYPE enumtype ADD 'newlabel' > it might be better to have: > ALTER TYPE enumtype ADD LABEL 'newlabel' > That way if we later wanted to support some other sort of ADD operation > on types we would be able to more easily. I can see the point of that, but I don't find LABEL to be a particularly great name for the elements of an enum type, and so I'm not in favor of institutionalizing that name in the syntax. How about ADD VALUE? 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] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 12:51 PM, Jeff Davis wrote: > On Sun, 2010-10-24 at 18:59 -0400, Tom Lane wrote: >> Jeff Davis writes: >> > Last development cycle, one of the questions that was unresolved was >> > whether to handle ranges like a discrete set (that is, [1,5) = [1,4] ) >> > or continuous or both. >> >> Put me in the camp that says you need both. I really seriously dislike >> the idea of representing [1, 2) as [1, 2-epsilon], mainly because there >> is often no portable value for epsilon. Dump-and-restore would be quite >> hazardous. >> > > OK. I tried to present a couple approaches for achieving that. To > summarize: > > The most obvious way would be different code paths and DDL options that > let postgresql know whether it's continuous or discrete. That may make > it easier to create new range types with just DDL and without defining > any low-level functions, and postgresql may be able to take care of > representational issues. > > Another way, suggested by Nathan Boley, is to require the type > definition to do a lot of work and define its own representation that's > opaque to postgres. Then, postgres would ask for information through > accessors like min (null if open at beginning), max (null if open at > end), upper bound, lower bound, and flags (to indicate null or infinite > boundaries). This requires more work to define a new range type, and it > certainly couldn't be done with DDL only. However, it seems to allow > discrete and continuous ranges to work together more seamlessly and > share more code. I am leaning toward this approach. I'm still confused. It seems to me (and maybe I'm full of it) that the distinction between continuous ranges and discrete ranges is pretty minor. Suppose you have continuous ranges done, and working. The only thing you need to add for discrete ranges (I think) is a canonicalization function that converts a range with one or both ends open to a range with both ends closed. Then you just apply this canonicalization functions to every value supplied by the user before doing anything else with it. Poof, discrete ranges! What am I missing? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] O_DSYNC broken on MacOS X?
On mån, 2010-10-25 at 09:33 -0400, Robert Haas wrote: > It seems we're still missing some relevant details, because hdparm > doesn't seem to work on SCSI devices. Is sdparm the right utility in > that case? Does anyone know what the correct incantations look like? Search the sdparm man page for "Writeback Cache". It has detailed examples. -- 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] Range Types, discrete and/or continuous
On Sun, 2010-10-24 at 18:59 -0400, Tom Lane wrote: > Jeff Davis writes: > > Last development cycle, one of the questions that was unresolved was > > whether to handle ranges like a discrete set (that is, [1,5) = [1,4] ) > > or continuous or both. > > Put me in the camp that says you need both. I really seriously dislike > the idea of representing [1, 2) as [1, 2-epsilon], mainly because there > is often no portable value for epsilon. Dump-and-restore would be quite > hazardous. > OK. I tried to present a couple approaches for achieving that. To summarize: The most obvious way would be different code paths and DDL options that let postgresql know whether it's continuous or discrete. That may make it easier to create new range types with just DDL and without defining any low-level functions, and postgresql may be able to take care of representational issues. Another way, suggested by Nathan Boley, is to require the type definition to do a lot of work and define its own representation that's opaque to postgres. Then, postgres would ask for information through accessors like min (null if open at beginning), max (null if open at end), upper bound, lower bound, and flags (to indicate null or infinite boundaries). This requires more work to define a new range type, and it certainly couldn't be done with DDL only. However, it seems to allow discrete and continuous ranges to work together more seamlessly and share more code. I am leaning toward this approach. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
Hello
I am thinking, so it isn't possible. There are a general datatype
anyelement, but it cannot accept a second general type record.
CREATE TYPE p AS (a text, b int, c bool);
CREATE OR REPLACE FUNCTION fp(p)
RETURNS int AS $$
BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b;
END;
$$ LANGUAGE plpgsql;
postgres=# select fp(row('hello',10, false));
NOTICE: a = hello
fp
10
(1 row)
Regards
Pavel Stehule
Time: 0.814 ms
postgres=# select fp(row('hello',10, false));
2010/10/25 Greg
> Hi guys, got across an interesting problem of passing params to a function
> in postgre: is it possible to pass a composite parameter to a function
> without declaring a type first?
>
> For example:
>
> // declare a function
> create function TEST ( object??? )
>
>object???.paramName// using parameter
>
>
> // calling
> perform TEST( ROW(string, int, bool, etc...) )
>
> Or do I have to do the type declaration for that parameter?
>
> Thanks!
>
>
Re: [HACKERS] add label to enum syntax
On mån, 2010-10-25 at 11:48 -0400, Andrew Dunstan wrote: > It occurred to me in the dead of the night that instead of: > > ALTER TYPE enumtype ADD 'newlabel' > > > it might be better to have: > > ALTER TYPE enumtype ADD LABEL 'newlabel' That had occurred to me as well. Go for it. -- 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] Segfault in 9.0 inlining SRF
Brendan Jurd writes: > On 25 October 2010 07:36, Tom Lane wrote: >> I'm guessing it was modified in the temporary memory context and not >> properly copied out to the parent context when we finished inlining >> the function. > Thanks for the hint; I found that the attached patch resolved my > specific segfault, but I am wondering whether it goes far enough. The > patch just copies invalItems up out of the temporary context before it > is deleted. Could there also be changes to other elements of > PlannerGlobal that need to be saved? Should we in fact be copying out > the whole of PlannerGlobal each time, and would that necessitate a new > copyfunc for it? Well, it definitely doesn't go far enough, because the invalItems list has to be restored to its original state if we fail to inline at some point after calling eval_const_expressions. I'm currently testing the attached patch. I thought about whether we need something more general, but for the moment I think this is sufficient; eval_const_expressions has only very limited reason to examine the PlannerInfo data at all, and less reason to modify it. Copying the whole structure would be overkill. Moreover, it wouldn't do anything to improve modularity anyhow: this function would still have to know which parts of the structure to copy back to the top level, and which not. So it'd still need to know quite a bit about exactly what eval_const_expressions is doing. regards, tom lane diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 13e89ec6678f01e7baf39f722aa960bf4550261b..00156d8eeeadfc18ad7680d283dc14a4db641f62 100644 *** a/src/backend/optimizer/util/clauses.c --- b/src/backend/optimizer/util/clauses.c *** rowtype_field_matches(Oid rowtypeid, int *** 2018,2028 * will not be pre-evaluated here, although we will reduce their * arguments as far as possible. * * We assume that the tree has already been type-checked and contains * only operators and functions that are reasonable to try to execute. * * NOTE: "root" can be passed as NULL if the caller never wants to do any ! * Param substitutions. * * NOTE: the planner assumes that this will always flatten nested AND and * OR clauses into N-argument form. See comments in prepqual.c. --- 2018,2033 * will not be pre-evaluated here, although we will reduce their * arguments as far as possible. * + * Whenever a function is eliminated from the expression by means of + * constant-expression evaluation or inlining, we add the function's + * OID to root->glob->invalItems. This ensures the plan is known to + * depend on such functions, even though they aren't referenced anymore. + * * We assume that the tree has already been type-checked and contains * only operators and functions that are reasonable to try to execute. * * NOTE: "root" can be passed as NULL if the caller never wants to do any ! * Param substitutions nor receive info about inlined functions. * * NOTE: the planner assumes that this will always flatten nested AND and * OR clauses into N-argument form. See comments in prepqual.c. *** inline_set_returning_function(PlannerInf *** 4095,4100 --- 4100,4106 boolmodifyTargetList; MemoryContext oldcxt; MemoryContext mycxt; + List *saveInvalItems; inline_error_callback_arg callback_arg; ErrorContextCallback sqlerrcontext; List *raw_parsetree_list; *** inline_set_returning_function(PlannerInf *** 4181,4186 --- 4187,4202 ALLOCSET_DEFAULT_MAXSIZE); oldcxt = MemoryContextSwitchTo(mycxt); + /* +* When we call eval_const_expressions below, it might try to add items +* to root->glob->invalItems. Since it is running in the temp context, +* those items will be in that context, and will need to be copied out +* if we're successful. Temporarily reset the list so that we can keep +* those items separate from the pre-existing list contents. +*/ + saveInvalItems = root->glob->invalItems; + root->glob->invalItems = NIL; + /* Fetch the function body */ tmp = SysCacheGetAttr(PROCOID, func_tuple, *** inline_set_returning_function(PlannerInf *** 4307,4312 --- 4323,4331 querytree = copyObject(querytree); + root->glob->invalItems = list_concat(saveInvalItems, + copyObject(root->glob->invalItems)); + MemoryContextDelete(mycxt); error_context_stack = sqlerrcontext.previous; ReleaseSysCache(func_tuple); *** inline_set_returning_function(PlannerInf *** 4322,4327 --- 4341,4347
Re: [HACKERS] add label to enum syntax
On Mon, Oct 25, 2010 at 11:57 AM, Pavel Stehule wrote: > 2010/10/25 Andrew Dunstan : >> >> It occurred to me in the dead of the night that instead of: >> >> ALTER TYPE enumtype ADD 'newlabel' >> >> >> it might be better to have: >> >> ALTER TYPE enumtype ADD LABEL 'newlabel' > > +1 +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Composite Types and Function Parameters
Hi guys, got across an interesting problem of passing params to a function in postgre: is it possible to pass a composite parameter to a function without declaring a type first? For example: // declare a function create function TEST ( object??? ) object???.paramName // using parameter // calling perform TEST( ROW(string, int, bool, etc...) ) Or do I have to do the type declaration for that parameter? Thanks!
Re: [HACKERS] add label to enum syntax
2010/10/25 Andrew Dunstan : > > It occurred to me in the dead of the night that instead of: > > ALTER TYPE enumtype ADD 'newlabel' > > > it might be better to have: > > ALTER TYPE enumtype ADD LABEL 'newlabel' +1 Regards Pavel > > > That way if we later wanted to support some other sort of ADD operation on > types we would be able to more easily. LABEL is already a keyword, so it > should be pretty minimally invasive to make this change, and if we want to > do it now is the time. > > Thoughts? > > 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] add label to enum syntax
It occurred to me in the dead of the night that instead of: ALTER TYPE enumtype ADD 'newlabel' it might be better to have: ALTER TYPE enumtype ADD LABEL 'newlabel' That way if we later wanted to support some other sort of ADD operation on types we would be able to more easily. LABEL is already a keyword, so it should be pretty minimally invasive to make this change, and if we want to do it now is the time. Thoughts? 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: [HACKERS] [BUGS] Segfault in 9.0 inlining SRF
On 25 October 2010 07:36, Tom Lane wrote:
> Brendan Jurd writes:
>> I have encountered a reproducible segfault in Postgres ...
>
> Looks like the invalItems list has been clobbered:
>
> (gdb) p *root->glob->invalItems
> $6 = {type = 2139062143, length = 2139062143, head = 0x7f7f7f7f,
> tail = 0x7f7f7f7f}
>
> I'm guessing it was modified in the temporary memory context and not
> properly copied out to the parent context when we finished inlining
> the function.
>
Hi Tom,
Thanks for the hint; I found that the attached patch resolved my
specific segfault, but I am wondering whether it goes far enough. The
patch just copies invalItems up out of the temporary context before it
is deleted. Could there also be changes to other elements of
PlannerGlobal that need to be saved? Should we in fact be copying out
the whole of PlannerGlobal each time, and would that necessitate a new
copyfunc for it?
Cheers,
BJ
diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 13e89ec..b39ebb6 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4300,12 +4300,13 @@ inline_set_returning_function(PlannerInfo *root,
RangeTblEntry *rte)
fexpr->args);
/*
-* Copy the modified query out of the temporary memory context, and
clean
-* up.
+* Copy the modified query, and the possibly-altered global invalidation
+* list, out of the temporary memory context, and clean up.
*/
MemoryContextSwitchTo(oldcxt);
querytree = copyObject(querytree);
+ root->glob->invalItems = copyObject(root->glob->invalItems);
MemoryContextDelete(mycxt);
error_context_stack = sqlerrcontext.previous;
--
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] Range Types, discrete and/or continuous
On Mon, Oct 25, 2010 at 10:21:49AM -0400, Tom Lane wrote: > David Fetter writes: > > On Sun, Oct 24, 2010 at 06:59:34PM -0400, Tom Lane wrote: > >> Put me in the camp that says you need both. I really seriously > >> dislike the idea of representing [1, 2) as [1, 2-epsilon], mainly > >> because there is often no portable value for epsilon. > >> Dump-and-restore would be quite hazardous. > > > It wouldn't be stored as (1, 2-epsilon). It would be stored more > > like (1, 2, closed, open). > > Fine ... > > > If you have a coherent, worked-out theory of continuous ranges, > > please feel free to develop and publish it just as Snodgrass, et > > al., have done with discrete ranges, but please *don't* feel free > > to assume that you can just wave a magic wand and make continuous > > time ranges "just work" because it pleases you aesthetically. > > That is FUD, and nothing more. If you know a concrete reason why > Postgres shouldn't provide both closed and open ranges, you need to > explain it, not claim that there might be a reason someplace and > it's someone else's problem to prove your point for you. That you're confusing the open/closed ranges with discrete/continuous tells me that it's *you* who doesn't understand the issues at hand. If you can present or develop a coherent theory of continuous ranges, that's great. If you can present or develop a theory that merges that one with discrete ranges, that's great too. Until then, let's get the discrete ranges going and disallow ones based on the continuum, i.e. floats. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bug in explain - core dump
postgres=# \i crash.sql version ── PostgreSQL 9.1devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4… …-10), 64-bit (1 row) Time: 0.979 ms DROP TABLE Time: 53.507 ms CREATE TABLE Time: 47.260 ms INSERT 0 10 Time: 6877.858 ms ANALYZE Time: 58.261 ms CREATE INDEX Time: 312.896 ms CREATE INDEX Time: 236.879 ms psql:crash.sql:13: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:crash.sql:13: connection to server was lost Regards Pavel Stehule SELECT version(); DROP TABLE IF EXISTS foo; CREATE TABLE foo(a int, b int); INSERT INTO foo SELECT (random()*1)::int, (random()*10)::int from generate_series(1,10); ANALYZE foo; CREATE INDEX ON foo(a,b); CREATE INDEX ON foo(b,a); EXPLAIN SELECT max(a), b FROM foo, generate_series(0,30) g(v) WHERE a = (SELECT max(a) FROM foo WHERE b = v) GROUP BY b; /* sometimes necessary call more times * result without crash postgres=# explain select max(a), b from foo, generate_series(0,30) g(v) where a = (select max(a) from foo where b = v) group by b; QUERY PLAN ─ HashAggregate (cost=445.57..445.58 rows=1 width=8) -> Hash Join (cost=22.50..445.54 rows=5 width=8) Hash Cond: (public.foo.a = (SubPlan 2)) -> Index Scan using foo_b_a_idx on foo (cost=0.00..8.28 rows=1 width=8) -> Hash (cost=10.00..10.00 rows=1000 width=4) -> Function Scan on generate_series g (cost=0.00..10.00 rows=1000 width=4) SubPlan 2 -> Result (cost=8.28..8.29 rows=1 width=0) InitPlan 1 (returns $1) -> Limit (cost=0.00..8.28 rows=1 width=4) -> Index Scan Backward using foo_a_b_idx1 on foo (cost=0.00..8.28 rows=1 width=4) Index Cond: ((a IS NOT NULL) AND (b = g.v)) (12 rows) * */ EXPLAIN SELECT max(a), b FROM foo, generate_series(0,30) g(v) WHERE a = (SELECT max(a) FROM foo WHERE b = v) GROUP BY b; -- 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] Extensible executor nodes for preparation of SQL/MED
Itagaki Takahiro writes: > SQL/MED will have some kinds of planner hooks to support FDW-depending > plan execution. Then, we will need to support user-defined executor nodes. > The proposed SQL/MED has own "executor node hooks" in ForeignTableScan, > http://wiki.postgresql.org/wiki/SQL/MED#Executor > but I think it will be cleaner to support it in executor level. I think the argument that this is good for FDW is bogus: there is no evidence whatsoever that we need add-on plan node types, and if we did need them, we'd need a whole lot more infrastructure than what you're sketching (see EXPLAIN for instance, not to mention how will the planner generate them in the first place). But it might be a good change anyway from a performance standpoint, in case a call through a function pointer is faster than a big switch. Have you tried benchmarking it on common platforms? One comment is that as sketched, this requires two extra levels of indirection at runtime, for no particular benefit that I can see. It'd be better to put the function pointers right in the planstate nodes, at least for the most common case of ExecProcNode. 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] Extensions, this time with a patch
Excerpts from Alvaro Herrera's message of lun oct 25 10:37:22 -0300 2010: > Excerpts from Alvaro Herrera's message of vie oct 22 17:02:22 -0300 2010: > > > > I'll go rework the patch sometime later to drop the name from the > > > control file, but that also means fixing several contrib modules by > > > changing their file names, operation for which the project has no policy > > > yet as far as I understand (we used CVS before). > > > > Change what file names? You lost me there. I thought the extension > > name was going to be equal to the control file name, and said control > > file doesn't exist yet, so you don't need to rename any existant file. > > Am I confusing something? > > Hmm, after reading the latest blog post, it seems that the patch > requires that the control file is equal to the .sql install script. Is > this the case? I don't see a reason for this requirement; why not > simply have a line for the install script in the control file? That > way, you don't need to rename the .sql files. Ah, some reading of the patch reveals that the "script" defaults to the control file name, but it can be overridden. I noticed that you're using ExclusiveLock when creating an extension, citing the handling of the global variable create_extension for this. There are two problems here: one is that you're releasing the lock way too early: if you wanted this to be effective, you'd need to hold on to the lock until after you've registered the extension. The other is that there is no need for this at all, because this backend cannot be concurrently running another CREATE EXTENSION comand, and this is a backend-local variable. So there's no point. Why palloc create_extension every time? Isn't it better to initialize it properly and have a boolean value telling whether it's to be used? Also, if an extension fails partway through creation, the var will be left set. I think you need a PG_TRY block to reset it. (I find the repeated coding pattern that tests create_extension for NULL-ness before calling recordDependencyOn a bit awkward; maybe hide it in a function or macro? But then maybe that's just me. Also, why palloc it? Seems better to have it static. Notice your new calls to recordDependencyOn are the only ones with operands not using the & operator.) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] bug in explain - core dump
Pavel Stehule writes: > On 9.1 I found a query where explain do backend crash Can't reproduce here. Would you provide a self-contained test 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] Range Types, discrete and/or continuous
David Fetter writes: > On Sun, Oct 24, 2010 at 06:59:34PM -0400, Tom Lane wrote: >> Put me in the camp that says you need both. I really seriously >> dislike the idea of representing [1, 2) as [1, 2-epsilon], mainly >> because there is often no portable value for epsilon. >> Dump-and-restore would be quite hazardous. > It wouldn't be stored as (1, 2-epsilon). It would be stored more like > (1, 2, closed, open). Fine ... > If you have a coherent, worked-out theory of continuous ranges, please > feel free to develop and publish it just as Snodgrass, et al., have > done with discrete ranges, but please *don't* feel free to assume that > you can just wave a magic wand and make continuous time ranges "just > work" because it pleases you aesthetically. That is FUD, and nothing more. If you know a concrete reason why Postgres shouldn't provide both closed and open ranges, you need to explain it, not claim that there might be a reason someplace and it's someone else's problem to prove your point for you. I don't have any problem with specific operations failing for open-ended ranges, if there isn't a meaningful result for the case; but that doesn't lead me to the conclusion that every operation is meaningless for open-ended ranges. >> But the real problem is that if the user wants to think in terms of >> continuous ranges, the only way that he can convert those to >> discrete ranges is to assume an epsilon for the datatype, and he >> shouldn't be forced to do that; not even if the datatype does have a >> well-defined epsilon at the implementation level, which several of >> ours don't.. > They're all well defined, but not uniform. And that's not even FUD, it's simply wrong. Even if you're prepared to claim that users should understand the precise behavior of their local floating-point type, what about NUMERIC? 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] Extensions, this time with a patch
Excerpts from Alvaro Herrera's message of vie oct 22 17:02:22 -0300 2010: > Excerpts from Dimitri Fontaine's message of vie oct 22 16:21:14 -0300 2010: > > I'll go rework the patch sometime later to drop the name from the > > control file, but that also means fixing several contrib modules by > > changing their file names, operation for which the project has no policy > > yet as far as I understand (we used CVS before). > > Change what file names? You lost me there. I thought the extension > name was going to be equal to the control file name, and said control > file doesn't exist yet, so you don't need to rename any existant file. > Am I confusing something? Hmm, after reading the latest blog post, it seems that the patch requires that the control file is equal to the .sql install script. Is this the case? I don't see a reason for this requirement; why not simply have a line for the install script in the control file? That way, you don't need to rename the .sql files. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] O_DSYNC broken on MacOS X?
On Tue, Oct 19, 2010 at 11:06 AM, Bruce Momjian wrote: > Robert Haas wrote: >> On Thu, Oct 7, 2010 at 11:52 AM, Tom Lane wrote: >> > Robert Haas writes: >> >> Proposed doc patch attached. >> > >> > "discusesed"? ?Otherwise +1 >> >> Woops, thanks. Committed with that change. I back-patched it back to >> 8.3, which is as far as it applied with only minor conflicts. > > I have applied the attached patch which mentions tools/fsync for testing > fsync method performance, and clarified the new paragraph about sync > methods. > > I am glad to see we are beefing up this area of the docs. It seems we're still missing some relevant details, because hdparm doesn't seem to work on SCSI devices. Is sdparm the right utility in that case? Does anyone know what the correct incantations look like? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] find -path isn't portable
Excerpts from Alvaro Herrera's message of jue oct 21 18:06:40 -0300 2010: > Excerpts from Tom Lane's message of jue oct 21 17:48:18 -0300 2010: > > buildfarm member koi, having recently been rescued from git purgatory, > > is failing like this: > > which indicates it can't cope with your recent patch to prep_buildtree. > > I was afraid that might be a problem. Can we please stick to the > > find(1) options defined by the Single Unix Spec? > > http://www.opengroup.org/onlinepubs/007908799/xcu/find.html > > Ugh. I'll look into this. I used grep -v instead. Koi has been fixed in a different way, however; maybe they installed GNU find. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] SQL/MED with simple wrappers
Hello > 4) List of foreign connections > Users (especially DBAs?) might want to see list of foreign connections. > Currently postgresql_fdw provides its own connection list via > postgresql_fdw_connections view. Common view such as > pg_foreign_connections would be needed? If so, function which returns > list of active connections would be necessary in FDW API. > + list of foreign tables? > 5) Routine mapping > If a function in local query can be evaluated on the remote side in > same semantics, it seems efficient to push the function down to the > remote side. But how can we know whether the function can be pushed > down or not? For such purpose, SQL/MED standard defines "routine > mapping". Should we implement routine mapping? > is it related to aggregate functions? If yes, this it can be really significant help > > 7) Using cursor in postgresql_fdw > postgresql_fdw fetches all of the result tuples from the foreign > server in the first pgIterate() call. It could cause out-of-memory if > the result set was huge. If libpq supports protocol-level cursor, > postgresql_fdw will be able to divide result set into some sets and > lower the usage of memory. Or should we use declare implicit cursor > with DECLARE statement? One connection can be used by multiple > ForeignScan nodes in a local query alternately. An issue is that > cursor requires implicit transaction block. Is it OK to start > transaction automatically? I don't know why DECLARE statement is problem? Can you explain it, please. regards Pavel Stehule -- 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] security hook on authorization
Sorry for this late responding. I got a cold later half of the last week. (2010/10/20 12:10), Robert Haas wrote: On Wed, Oct 13, 2010 at 2:13 AM, Robert Haas wrote: 2010/8/24 KaiGai Kohei: I tried to revise the patch. It allows plugins to get control next to client authentication, but before returning the status to users. This change enables plugins which should be invoked on authentication failed to utilize this hook, not only assignment of session security label. At the same time, it disables to hook on SET SESSION AUTHORIZATION. But it is a bit unclear whether we should hook here, or not. Stephen - You've been listed as a reviewer for this in the CF app since 9/17 - are you planning to review it? I guess not. I took a brief look at this tonight, and it seems to me that it still fails the test I proposed nearly two months ago: http://archives.postgresql.org/pgsql-hackers/2010-08/msg01458.php KaiGai responded with: If and when a connection came from a host but we don't accept the delivered security label, or labeled networking is misconfigured, getpeercon(3) returns NULL. In this case, server cannot identify what label should be applied on the client, then, we should disconnect this connection due to the error on database login, not any access control decision. In similar case, psm_selinux.so disconnect the connection when it cannot identify what security label shall be assigned on the session, due to some reasons such as misconfigurations. Without any hooks at authorization stage (but it might be different place from this patch, of course), we need to delay the error handling by the time when SE-PostgreSQL module is invoked at first. But it is already connection established and user sends a query. It seems to me quite strange behavior. I don't find this very convincing. We are still several patches from having a working SE-PostgreSQL module, and I think we should be worried about getting off the ground before we worry about this sort of fine-tuning. I don't see reporting an SE-PostgreSQL error slightly sooner is worth a separate hook, especially given that we're still several patches from having even a toy SE-PostgreSQL implementation. For example, we may find that some other hook that is more certainly necessary can also serve the purpose intended for this one. At least, we need a feature to raise an error when the SE-PgSQL module cannot retrieve security context of the peer process, because it is similar to a connection string without username/password. However, the post-authentication hook is not the only option for us. What I want here is the core PG gives the SE-PgSQL module a chance to call getpeercon(3) before accepting user's queries. One possible candidate is CheckMyDatabase() that checks ACL_CONNECT permission for the required database prior to execution of all the queries. Currently, we don't have any security hook around here. But, if we have "InvokeSecurityHook()" here, it will be able to kill two birds with one stone. The 1st bird is getpeercon(3), and the 2nd bird is permission check on the selected database. And later with: Yes, I also think this kind of authorization hook should benefit other applications, not only label based mac features. For example, something like 'last' command in operations system which records username and login-time. Stephen mentioned pam_tally that locks down certain accounts who failed authentication too much. Perhaps, PAM modules in operating system give us some hints about other possible applications. This is closer to the mark, but mostly speculative, and not detailed enough to determine whether the proposed hook is properly located. It seems rather early to me: this is before we've sent the authentication packet to the client, so we couldn't, for example, log the success or failure of the authentication; we don't know whether it will succeed or fail. Hmm. But the auth_failed() raises a fatal error, so we need to put a hook before the invocation to log a case of authentication failed. | + if (ClientAuthentication_hook) | + (*ClientAuthentication_hook)(port, status); | + | if (status == STATUS_OK) | sendAuthRequest(port, AUTH_REQ_OK); | else | auth_failed(port, status); Or, perhaps, we should modify this if-block to ensure the hook being called after sendAuthRequest() but before auth_failed(). I am going to mark this Returned with Feedback. I would like to request that any future submissions to add a hook in this area be accompanied by a working sample contrib module that is not SE-Linux specific. For example, if a contrib module provides a feature to sleep a few seconds when authentication failed, it prevents brute-force attack. Do you think it is a good example as an evidence of this module? Anyway, I have any preference on these two ideas right now. It seems to me the contrib module will be small enough, but well works as proof of concept. On the other hand, eventually we will put
[HACKERS] bug in explain - core dump
Hello On 9.1 I found a query where explain do backend crash backtrace Core was generated by `postgres: pavel postgres [local] EXPLAIN '. Program terminated with signal 6, Aborted. #0 0x003226c329a5 in raise () from /lib64/libc.so.6 Missing separate debuginfos, use: debuginfo-install glibc-2.12.1-3.x86_64 (gdb) bt #0 0x003226c329a5 in raise () from /lib64/libc.so.6 #1 0x003226c34185 in abort () from /lib64/libc.so.6 #2 0x006eedad in ExceptionalCondition (conditionName=, errorType=, fileName=, lineNumber=) at assert.c:57 #3 0x00693870 in print_parameter_expr (expr=0xe72e40, ancestor_cell=, dpns=0xe75258, context=0x7fffaf057790) at ruleutils.c:4386 #4 0x006929c6 in get_oper_expr (node=0xe742c8, context=0x7fffaf057790, showimplicit=) at ruleutils.c:5655 #5 get_rule_expr (node=0xe742c8, context=0x7fffaf057790, showimplicit=) at ruleutils.c:4820 #6 0x006914f6 in get_rule_expr (node=0xe74cb8, context=0x7fffaf057790, showimplicit=) at ruleutils.c:4873 #7 0x00697dcd in deparse_expression_pretty (expr=0xe74cb8, dpcontext=0xe752d8, forceprefix=, showimplicit=0 '\000', prettyFlags=0, startIndent=0) at ruleutils.c:2086 #8 0x0052a294 in show_expression (node=0xe74cb8, qlabel=0x81e376 "Index Cond", planstate=, ancestors=, useprefix=0 '\000', es=0x7fffaf057e30) at explain.c:1330 #9 0x0052b62e in ExplainNode (planstate=0xe76a18, ancestors=0xe74930, relationship=0x81e531 "Outer", plan_name=, es=0x7fffaf057e30) at explain.c:1018 #10 0x0052b049 in ExplainNode (planstate=0xe766d8, ancestors=0xe74930, relationship=0x81e528 "InitPlan", plan_name=, es=0x7fffaf057e30) at explain.c:1203 #11 0x0052b01f in ExplainSubPlans (planstate=0xe77c80, ancestors=0xe74930, relationship=0x81e546 "SubPlan", plan_name=, es=0x7fffaf057e30) at explain.c:1687 #12 ExplainNode (planstate=0xe77c80, ancestors=0xe74930, relationship=0x81e546 "SubPlan", plan_name=, es=0x7fffaf057e30) at explain.c:1199 #13 0x0052b168 in ExplainSubPlans (planstate=0xe7a018, ancestors=0xe74930, relationship=0x81e537 "Inner", plan_name=, es=0x7fffaf057e30) at explain.c:1687 #14 ExplainNode (planstate=0xe7a018, ancestors=0xe74930, relationship=0x81e537 "Inner", plan_name=, es=0x7fffaf057e30) at explain.c:1249 #15 0x0052b064 in ExplainNode (planstate=0xe78420, ancestors=0xe74930, relationship=0x81e531 "Outer", plan_name=, es=0x7fffaf057e30) at explain.c:1208 #16 0x0052b049 in ExplainNode (planstate=0xe78578, ancestors=0xe74930, relationship=0x0, plan_name=, es=0x7fffaf057e30) at explain.c:1203 #17 0x0052c4c7 in ExplainOnePlan (plannedstmt=, es=0x7fffaf057e30, queryString=, params=0x0) at explain.c:411 #18 0x0052c9cb in ExplainOneQuery (stmt=0xd78fe8, queryString=, params=0x0, dest=0xe29288) at explain.c:303 #19 ExplainQuery (stmt=0xd78fe8, queryString=, params=0x0, dest=0xe29288) at explain.c:209 #20 0x00638007 in PortalRunUtility (portal=0xe7e538, utilityStmt=0xd78fe8, isTopLevel=1 '\001', dest=0xe29288, completionTag=0x7fffaf057ee0 "") at pquery.c:1191 #21 0x006393cc in FillPortalStore (portal=0xe7e538, isTopLevel=1 '\001') at pquery.c:1065 #22 0x00639997 in PortalRun (portal=0xe7e538, count=9223372036854775807, isTopLevel=1 '\001', dest=0xe81050, altdest=0xe81050, completionTag=0x7fffaf0580c0 "") at pquery.c:791 #23 0x00635d69 in exec_simple_query ( query_string=0xd77108 "explain select max(a), b from foo, generate_series(0,30) g(v) where a = (select max(a) from foo where b = v) group by b;") at postgres.c:1052 #24 0x00636d20 in PostgresMain (argc=, argv=, username=) at postgres.c:3869 #25 0x005fb811 in BackendRun () at postmaster.c:3556 postgres=# \d foo Table "public.foo" Column │ Type │ Modifiers ┼─┼─── a │ integer │ b │ integer │ Indexes: "foo_a_b_idx" btree (a, b) "foo_a_idx" btree (a) "foo_b_a_idx" btree (b, a) "foo_b_idx" btree (b) "foo_b_idx1" btree (b) statement: explain select max(a), b from foo, generate_series(0,30) g(v) where a = (select max(a) from foo where b = v) group by b; regards Pavel Stehule -- 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] ask for review of MERGE
Robert Haas wrote:
> What we're talking about is what happens when there are concurrent
> table modifications in progress; and the answer is that you might
> get serialization anomalies. But we have serialization anomalies
> without MERGE, too - see the discussions around Kevin Grittner's
> SSI patch which, come to think of it, might be useful for this
> case, too.
I've been thinking about that as I read the discussion. If both
transactions are serializable, there would tend to be a default
behavior of preventing anomalies. ("Tend to be" because it might
actually require the addition of a few calls to predicate locking
functions from new MERGE code to be consistent with other code under
the patch.)
On the other hand, where there is a more targeted way of protecting
integrity, I've tried to keep SSI out of the way and let it function
as it has. For example, foreign key enforcement already manages
this, so the SSI implementation intentionally ignores those reads and
writes. From the discussion on MERGE I've been thinking that where
there is an appropriate unique index the SSI code might want to stay
out of the way, similar to foreign keys; but it might be used to
handle the cases where there is no appropriate index. Or possibly
the predicate locking portion of it could be used in a novel way by
MERGE code to implement the MERGE logic. The API for SSI basically
involves three types of functions:
- Acquire a predicate lock on an object.
- Check whether a given object is predicate locked.
- Check for rw-conflict.
To be useful for MERGE, that second category would probably need to
be expanded, and we might need to upgrade btree index locking to
support range locking rather than stopping at index page locks. Dan
is planning to try this once he has sufficient benchmarks as a base
to confirm the performance impact.
> I posted an example upthread which I think demonstrates very
> clearly that MERGE will result in unavoidable serialization
> failures - so if the standard is that we mustn't have any
> serialization failures then the standard can never be met. The best
> we can hope for is that we'll detect them and roll back if they
> occur, rather than going on to commit but perhaps with some
> anomaly. And I'm pretty sure that's what KG's SSI patch is going to
> give us. So I'm not sure there's really anything to get worked up
> about here in terms of concurrency issues.
Given the standard's emphasis on data integrity and the known
concurrency issues with relational theory, I find it hard to believe
that the standard requires "no serialization failures duing merge" --
but I haven't had time to dig into the standard's specifications yet.
-Kevin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extensible executor nodes for preparation of SQL/MED
SQL/MED will have some kinds of planner hooks to support FDW-depending
plan execution. Then, we will need to support user-defined executor nodes.
The proposed SQL/MED has own "executor node hooks" in ForeignTableScan,
http://wiki.postgresql.org/wiki/SQL/MED#Executor
but I think it will be cleaner to support it in executor level.
The attached patch is an experimental code to do it; Plan struct has
"vtable" field as a set of functions to generate and execute PlanState
nodes. It changes large switch-case blocks in the current executor
into function-pointer calls as like as virtual functions in C++.
Is it worth doing? If we will go to the direction, I'll continue to
research it, like extensibility of Path nodes and EXPLAIN support.
Essence of the patch
typedef struct Plan
{
NodeTag type;
PlanVTable *vtable; /* executor procs */
...
struct PlanVTable
{
ExecInitNode_type InitNode;
ExecProcNode_type ProcNode;
MultiProcNode_type MultiProcNode;
ExecEndNode_typeEndNode;
...
make_seqscan()
{
node = makeNode(SeqScan);
node->vtable = &SeqScanVTable;
...
ExecReScan(node)
{
node->plan->vtable->ReScan(node);
...
--
Itagaki Takahiro
extensible_execnodes-20101025.patch.gz
Description: GNU Zip compressed data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, discrete and/or continuous
On 25.10.2010 01:59, Tom Lane wrote: Jeff Davis writes: If we treat those as discrete, then R1 = R2, R1 contains R2, R2 contains R1, and R2 - R1 = R1 - R2 = empty. However, if we treat those as continuous, then we get a contradiction: R2 contains R1 R1 does not contain R2 R2 - R1 = ( 2009-01-01 01:00:10, 2009-01-01 01:00:10.01 ) = empty? This is a circular argument: your conclusion that there's a contradiction in the concept of continuous ranges depends on the assumption that the datatype is discrete; and with such an assumption *of course* you can get a contradiction. Let's open that up a bit: >> R2 - R1 = ( 2009-01-01 01:00:10, 2009-01-01 01:00:10.01 ) Correct. >> ( 2009-01-01 01:00:10, 2009-01-01 01:00:10.01 ) = empty? No. The problem here is the unpack operator, ie. getting all discrete points within a range. It depends on the discreteness. I'm not sure what the ramifications of that are. It means that PACK(UNPACK(r)) != r, and I believe many of the other operators are defined in terms of pack/unpack, even though there's more practical implementations of them. Can we get away without pack/unpack? Can we define all the range operations without them? -- 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
