Re: [GENERAL] Streaming replication slave crash
On Mon, 2013-09-09 at 13:04 -0700, Mahlon E. Smith wrote: After some wild googlin' research, I saw the index visibility map fix for 9.2.1. We did pg_upgrade in-between versions, but just to be sure I wasn't somehow carrying corrupt data across versions (?), I went ahead and VACUUMed everythng with the vacuum_freeze_table_age set to 0, and went on with my life, hoping I had removed whatever demons were running around in there. You may have seen only partial information about that bug and the fix. See the first item in the release notes here: http://www.postgresql.org/docs/current/static/release-9-2-1.html And the actual fix here: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=beb850e1d873f8920a78b9b9ee27e9f87c95592f To rid the daemons entirely, after upgrading to 9.1+, you must REINDEX all btree and GIN indexes that may have been touched by 9.2.0. Since the actual problem you see is on an index (as you say later in your report), then I would suggest that you do that. Not 100% sure this is the root cause of your problem, of course, but the symptoms seem to line up. Unlike Quentin's original message, simply restarting the slave didn't bring it back to life. I had to pg_start_backup/rsync again from the master, at which point: It looks OK to me, so I think you are in the clear. If you are particularly unlucky, your master server crashed (while still on 9.2.0) without writing the data and left your master copy of the index corrupt. If you are worried about that, you can do another re-sync after you finish the REINDEXing. This is not necessary unless you experienced at least one crash on 9.2.0. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump/Reload pg_statistic to cut time from pg_upgrade?
On Mon, 2013-07-08 at 14:20 -0500, Jerry Sievers wrote:
Planning to pg_upgrade some large (3TB) clusters using hard link
method. Run time for the upgrade itself takes around 5 minutes.
Nice!! Origin version 8.4 and destination version 9.1.
Unfortunately the post-upgrade analyze of the entire cluster is going
to take a minimum of 1.5 hours running several threads to analyze all
tables. This was measured in an RD environment.
...
Anyway, perhaps there are other good reasons I should *not* attempt
this but it turns out that the stats table can't be reloaded with it's
own dump so this of course is a show-stopper.
psql:d:456: ERROR: cannot accept a value of type anyarray
CONTEXT: COPY pg_statistic, line 1, column stavalues1:
{_assets,_income,_liabilities,assets,income,liabilities}
[ late response, but might still be useful to someone ]
You can work around the problem with a little effort if you call
array_in directly. It takes the type output (cstring), element type
(oid), and element typmod (integer).
To dump the pg_statistics table, you have to output all of the columns
plus the type ID and the typmod, and then load it back in by doing
something like:
insert into pg_statistic
select starelid, ...,
array_in(stavalues1, the_element_type, -1), ...
from my_statistics_dump;
The element typmod is always -1 for pg_statistic. To get the element
type, you can join against pg_attribute. The only problem is, you don't
actually want the attribute type, you want the type used for the
statistics, which is normally the same but could be different. I don't
think the statypid is stored in the catalog, so you'd have to inventory
the types that you use and figure out a mapping of the type to it's
statistics type looking at the typanalyze routines.
So, it's possible to do, but not worth the effort unless you are quite
concerned about the analyze time post-upgrade.
It would be nice if we had a better way to backup, transfer, and upgrade
statistics. However, allowing statistics to be upgraded could be a
challenge if the statistics format changes between releases.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is NULL = unbounded for rangetypes?
On Fri, 2013-08-30 at 11:22 +0200, Andreas Joseph Krogh wrote: But I agree that returning NULL would be OK, then it would be easy to catch in queries when starting playing with range-types in queries. Having it implicitly mean infinity comes as a surprise, to me at least. Agreed. This was discussed at the time, and the original version of Range Types experimented with other means of specifying unbounded ranges in order to avoid this possible confusion. Unfortunately, everything we tried was awkward one way or another; and we eventually made the decision to go with greater convenience, even if it could cause some confusion. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding ip4r to Postgresql core?
On Wed, 2013-08-07 at 23:24 -0700, Chris Travers wrote: On Wed, Aug 7, 2013 at 9:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: So the obvious question today is whether this isn't duplicative of the range datatype stuff. IOW, why wouldn't we be better off to invent inetrange and call it good? INET allows a mask, and so doesn't offer an obvious total order. Interestingly, a mask is somewhat like a range, so perhaps we could use a range type that considers a mask to be an alternate representation of a range. I thought about that briefly, but it seemed more likely to lead to confusion or backwards-compatibility problems. If we just had an IP(v4|v6) type with no mask and a total order, adding a range type would be trivial. actually this misses the one area where ip4r is really helpful and that is GiST support. If you want to have an exclusion constraint which specifies that no two cidr blocks in a table can contain eachother, you can do this easily with ip4r but it takes a lot of work without it. A lot of work (much of it by Alexander Korotkov) has already gone into improving range type [sp-]gist indexes. And range types go together well with exclusion constraints. So, I'm not sure I follow how this is a reason to use ip4r rather than a range type -- can you clarify? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is NULL = unbounded for rangetypes?
On Tue, 2013-07-09 at 10:45 +0200, Andreas Joseph Krogh wrote: I would expect the queries above to return FALSE and have to use INFINITY to have them return TRUE. I don't understand what you mean by ranges not allowing either bound to be NULL as it seems to be the case (as in it works). Although passing NULL to the constructor works, it does *not* create a range where one bound is NULL. It actually creates an unbounded range; that is, a range where one bound is infinite. NULL semantics are far too confusing to be useful with ranges. For instance, if ranges did support NULLs; the queries you mention would have to return NULL, not FALSE. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is NULL = unbounded for rangetypes?
On Mon, 2013-07-08 at 10:19 +0200, Andreas Joseph Krogh wrote:
Hi.
Both of these queries return TRUE because NULL means unmounded:
select daterange('2013-07-01' :: DATE, null, '[]') daterange('2013-07-04'
:: DATE, '2013-07-30' :: DATE, '[]');
select daterange(null, '2013-08-11' :: DATE, '[]') daterange('2013-07-04'
:: DATE, '2013-07-30' :: DATE, '[]');
What is the rational behind this behavior of NULL?
It's just a convenience that passing NULL to a constructor creates an
unbounded range. The alternatives of having extra constructors for
unbounded ranges were discussed, but seemed more awkward.
Note that ranges do not allow either bound to be NULL. That would create
a lot of semantic problems.
Does that answer your question?
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] casting tsrange to tstzrange doesn't seem to work?
On Tue, 2013-06-11 at 14:05 -0700, Joe Van Dyk wrote: # select tsrange(null)::tstzrange; ERROR: cannot cast type tsrange to tstzrange LINE 1: select tsrange(null)::tstzrange; I agree that there should be a cast between tsrange and tstzrange. Unfortunately, this cant work generally for all range types, because the total order might be different. For instance, we can't cast between a textrange and int4range, because: ['09','1'] is a valid text range, but: [9,1] is not. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Money casting too liberal?
On Sat, 2013-03-30 at 09:52 -0400, D'Arcy J.M. Cain wrote: That's why I suggested that operations between money(2) and money(3) should raise an error. Treat them as distinct types. I don't think typmod is currently powerful enough to do that. It's lost in many different types of expressions. Offhand, I don't even know of a way to preserve the typmod through even a simple function. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Money casting too liberal?
On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote: If the money type is meant to be serious then these conventions need to be followed/settable on a column by column basis. I don't like the idea of tying the semantics to a column. That leaves out values that aren't stored in a column, e.g. literals or the results of some expression. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Money casting too liberal?
On Fri, 2013-03-29 at 16:30 -0400, D'Arcy J.M. Cain wrote: How would this be an issue? If you are assigning a literal to a column then that's no issue. Otherwise, a literal is simply a value that can be cast depending on the situation. The money type is no different in that regard. As a result of an expression, it will have the type of the data in the expression. What if the result is the addition of two columns of different precisions? Pick the higher precision? Forbid the operation? The latter may make sense. How can you add Yen and US$? Why not have various rounding functions that do exactly what you want? Then you can use them anywhere you want in an expression. Tying a bunch of magic to the column, I/O function, or type system just seems like the wrong approach when it comes to real differences (like precision). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FETCH in subqueries or CTEs
On Fri, 2012-08-24 at 09:35 +0800, Craig Ringer wrote: Hi all I've noticed that FETCH doesn't seem to be supported in subqueries or in CTEs. Is there a specific reason for that, beyond nobody's needed it and implemented it? I'm not complaining at all, merely curious. 1. Cursors have their own snapshot, so it would be kind of like looking at two snapshots of data at the same time. That would be a little strange. 2. For regular subqueries, it would also be potentially non-deterministic, because the FETCH operation has the side effect of advancing the cursor. So, if you had something like SELECT * FROM (FETCH 1 FROM mycursor) x WHERE FALSE, it's not clear whether the FETCH would execute or not. After the query, it may have advanced the cursor or may not have, depending on whether the optimizer decided it didn't need to compute the subquery. 3. Cursors are really meant for a more effective interaction with the client, it's not really meant as an operator (and it doesn't change the results, anyway). You can already do LIMIT/OFFSET in a subquery if you need that kind of thing. All that being said, there may be some use case for something like what you are describing, if you get creative. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some feedback on range types
On Wed, 2012-07-18 at 14:33 -0700, Scott Bailey wrote: I'm testing range types and I've come up with a couple of curiosities. 1) I'll start off easy. In the wild, discrete ranges tend to be closed-closed [] while continuous ranges tend to be closed-open [). For instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed [2009-06-01, 2012-04-15] or Sally lived [1934, 2001]. But these ranges are all converted to [). So Sally's tombstone ends up reading [1934-2002). Not a huge deal, but it is difficult for users to change this behavior. I don't really have a good answer for this. We could supply alternate output functions that allow you to specify how a discrete range is displayed. 2) Typemod doesn't work for subtypes. So say I'm working on a stock-trading app and I want to create a numeric range with a base type of numeric(8,2) and a granularity of 0.01. CREATE TYPE num_range AS RANGE (SUBTYPE = numeric(8,2)); SELECT num_range(0.2, 2/3.0); -- [0.2,0.6667) Will fix. I haven't gotten around to it yet; it's actually quite a bit of code (unless I'm missing something). 3) Continuing with the above example, I make a canonical function then hack it in to the system catalog to temporarily get around the chicken/egg problem mentioned earlier. CREATE OR REPLACE FUNCTION num_range_canonical(num_range) RETURNS num_range AS $$ SELECT num_range( (CASE WHEN lower_inc($1) THEN lower($1) ELSE lower($1) + 0.01 END)::numeric(8,2), (CASE WHEN upper_inc($1) THEN upper($1) ELSE upper($1) - 0.01 END)::numeric(8,2), '[]'); $$ LANGUAGE 'sql' IMMUTABLE STRICT; However, the built in range types are automatically canonicalized while a user created one is not, even with the canonical function set on the type. Not a huge problem, but not an expected behavior either. I assume that this isn't a problem when defining it in C using the method mentioned in the other thread. 4) No editing in place. This is a problem when trying to create functions that will work with anyrange. Some missing functionality was the ability to do set difference when the first range extends on both sides of the second. The function range_minus throws an exception in that situation. So I set about to add the functions range_ldiff and range_rdiff to pull out the left or right piece in this situation. Because users can add any number of range types it would be very to create a new instance of the correct type. It would be much easier to just edit the upper or lower bounds of one of the input parameters. But that doesn't seem to be supported. If I understand the problem correctly, it's a little more clear to solve it with the C API. Specifically, the range_get_typcache() and make_range() functions. You can see a similar pattern use in many of the generic range functions defined in rangetypes.c, like range_union(). I agree it would be nice to make it easier to define new range type functions with other PLs and not be so reliant on C. I like the idea of having functions that return a range of the same type but with some modification. Not quite update-in-place as you suggest, but accomplishes the same thing. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ignore hash indices on replicas
On Tue, 2012-07-10 at 00:09 -0700, Steven Schlansker wrote: I understand that the current wisdom is don't use hash indices, but (unfortunately?) I have benchmarks that show that our particular application is faster by quite a bit when a hash index is available. Can you publish the results somewhere? It might provoke some interest. I assume that fixing the hash index logging issue hasn't been a priority due to low interest / technical limitations, but I'm curious for a stopgap measure -- can we somehow configure Postgres to ignore hash indices on a replica, using other b-tree indices or even a sequential scan? I know I can do this on a per-connection basis by disabling various index lookup methods, but it'd be nice if it just ignored invalid indices on its own. This might work for you: http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantuner Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
On Fri, 2012-08-03 at 10:42 -0700, Jeff Davis wrote: On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote: great feature, but i can't find a TIMERANGE, i want to store time-ranges, for instance [10:00:00,16:00:00), how can i do that? CREATE TYPE timerange AS RANGE ( subtype = time ); That's the simple answer. I believe we discussed including this as a built-in range type at some point, but decided against it. I can't remember the reason right now. Time of day is a cycle (I forget who pointed this out), so a limit of 24:00:00 is fairly restrictive. It happens that daytime ranges like [14:00,15:00) are more common; but it doesn't seem unreasonable to say [22:00,02:00) either. So, an interpretation where time of day has a total order is only useful really for a daytime schedule (which is still useful, but perhaps not general enough to include in core). We might be able to make it work as ranges within a 24-hour cycle, but that will require more thought. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Range-Types in 9.2
On Fri, 2012-08-03 at 17:06 +0200, Andreas Kretschmer wrote: Hi all, great feature, but i can't find a TIMERANGE, i want to store time-ranges, for instance [10:00:00,16:00:00), how can i do that? CREATE TYPE timerange AS RANGE ( subtype = time ); That's the simple answer. I believe we discussed including this as a built-in range type at some point, but decided against it. I can't remember the reason right now. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice
On Thu, 2012-07-19 at 11:35 +0800, Craig Ringer wrote: The short version is that the person did a bulk-load of some PostGIS data using the osm2pgsql data-loader tool ( http://wiki.openstreetmap.org/wiki/Osm2pgsql) to populate a Pg 9.1 database. This appeared to succeed, but when the server was restarted it failed to come up, complaining that WAL contains references to invalid pages and page 1493172 of relation base/16385/477861 was uninitialized (for many different pages). The logs of the shutdown suggest that a backend probably crashed, but that shouldn't cause the WAL and heap corruption observed by the OP. Is it possible that the machine has write cache enabled? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)
On Mon, 2012-07-16 at 13:41 +0200, Alban Hertroys wrote: BTW, that second value looks a whole lot like a poorly thought out substitute for 'infinity' ... regards, tom lane That's certainly an interesting comment and I'm open to suggestions! The original db has two columns (from_timestamp, to_timestamp). I don't go for NULL in the to_timestamp column. Alternatively, a timestamp very, very far in the future can throw off query planners. Tom is telling you that there is a special timestamp 'infinity': Or, perhaps specify NULL for the upper bound, indicating that there is no upper bound and the range will be infinite. Note that this does not mean that the upper bound is NULL in the unknown sense, it means that there is no upper bound. Ranges have their own internal concept of unbounded ranges, so they work for other data types that don't have a concept of infinity (like integer). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
On Thu, 2012-06-28 at 21:41 +0700, tuanhoanganh wrote: Hello I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit. When i view Postgresql status, there are some autovaccum alway run. Ex VACUUM ANALYZE pg_catalog.pg_attribute. Is it problem of PostgreSQL? Please help me. Do you have activity on the database? If so, autovacuum is normal. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote: I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit or rollback required) but I would complain loudly if this were to be the default, and I don't see a real need for it. It's already available in psql. See ON_ERROR_ROLLBACK: http://www.postgresql.org/docs/9.2/static/app-psql.html Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] retrieving function raise messages in ecpg embedded sql code
On Mon, 2012-06-18 at 21:35 +, Haszlakiewicz, Eric wrote: I'm trying to get some additional information back from a trigger to my embedded SQL program, to essentially emulate Informix's way of generating serial values. I can get the serial to be generated, but I'm trying to figure out how to get the generated value back to my program with minimal changes to the SQL. Have you already looked at INSERT...RETURNING? http://www.postgresql.org/docs/9.2/static/sql-insert.html I can't figure out how to retrieve the message raised by the trigger. I know it's available in some cases, because I see the message when I insert a row through psql, but even things like this: printf(%s\n, PQerrorMessage(ECPGget_PGconn(mydb))); return nothing useful. Is there a way to get this information? Yes, these messages are delivered via notice processing (not to be confused with LISTEN/NOTIFY): http://www.postgresql.org/docs/9.2/static/libpq-notice-processing.html Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reference with inheritance propagate data
On Thu, 2012-06-14 at 13:08 +0400, Yuriy Rusinov wrote: We're need common numeration for primary key for all users tables, but others columns may be different for tables. Will a sequence shared between the two tables solve this problem? http://www.postgresql.org/docs/9.1/static/sql-createsequence.html Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reference with inheritance propagate data
On Wed, 2012-06-13 at 00:38 +0400, Yuriy Rusinov wrote: Hello, All ! I have base table q_base_table with column (id bigint) which may be inherited by users tables, primary key for both base table and derived tables is id. Now I need for another table record_rubricator which has to be referenced to base and derived tables, which way I have to do it, because postgresql does not allow automatically propagate data ? One foreign key cannot reference two tables. Have you considered a design that does not use inheritance? For instance, the users table could reference q_base_table, and then record_rubricator could also reference q_base_table? Also, I don't understand what you mean about propagating data. What data do you want to propagate? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trigger on view returning created serial
On Tue, 2012-06-12 at 22:35 +0200, Philipp Kraus wrote: Hello, I have created a view and on this view a trigger, which is called on an insert command on the view. Within the trigger I run an insert on a table and one of the table fields uses a serial sequence, that creates values. If I run in the trigger after the insert a return NEW, the field (id) which should be set by the serial on the table is 0, so how can I get the new created serial on the insert command within the trigger function? It's hard for me to tell exactly what problem you're describing, but it sounds similar to the one solved here: http://people.planetpostgresql.org/dfetter/index.php?/archives/66-VIEW-triggers-RETURNINGhtml If I misunderstood, please be more descriptive about what you are trying to do, what code you wrote, and what went wrong. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ctid ranges
On Fri, 2012-06-08 at 22:27 +0100, Thomas Munro wrote: This is slow, handled with a seq scan (as are various rephrasing with , =, etc): SELECT ... FROM ... WHERE ctid BETWEEN ... AND ...; Is there a way to retrieve the rows in a physical range quickly? Interesting idea. However, as far as I know, there is no such support. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Array fundamentals
On Sat, 2012-06-02 at 10:05 -0700, idc danny wrote:
Now, if I want do do the following:
select CombineStrings(ARRAY[SplitString2Array(SomeTextColumn), 'New
string to add']) from SomeTable
i get the following error:
array value must start with { or dimension information
This discussion is better suited to another list, like -general, so I'm
moving it there.
In the fragment:
ARRAY[SplitString2Array(SomeTextColumn), 'New string to add']
The first array element is itself an array of strings, but the second is
a plain string. Array elements must all be the same type.
What you want to do is replace that fragment with something more like:
array_append(SplitString2Array(SomeTextColumn), 'New string to add')
If that still doesn't work, we'll need to see the exact definitions of
your functions.
Also, as a debugging strategy, I recommend that you look at the pieces
that do work, and slowly build up the fragments until it doesn't work.
That will allow you to see the inputs to each function, and it makes it
easier to see why it doesn't work.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedural Languages
On Thu, 2012-05-31 at 11:52 -0600, David Salisbury wrote: I've often wondered how these external languages perform, figuring that using a native language would perform better. One language isn't more native than another, really. SQL is a bit more native in the sense that it might be inlined, and C is more native in the sense that it is native code. But PL/pgSQL just happens to be a good language when you are doing mostly SQL with some procedural aspects, it doesn't really have an inherent performance advantage over external PLs. There may be some implementation quality differences, however. If I'm executing say a PL/Perl procedure, once I've executed it the first time, can I take it the interpreter is now resident withing the PG footprint? Yes. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedural Languages
On Thu, 2012-05-31 at 11:23 -0700, Darren Duncan wrote: Michael Nolan wrote: PL/pgSQL and PL/perlu are the only ones I use. I use PL/perlu primarily to launch shell scripts from triggers, for example to update an external website when a row in a table has been inserted, deleted or updated. There is also another way to do what you describe that might be more secure. Rather than having the DBMS launch shell scripts directly, instead use LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an ordinary client script listening for them, and the client script launches the shell scripts when it gets a message. This way, you need a persistent client script, but you don't need to invoke the shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it was for. An additional advantage is that if you issue NOTIFY with exactly the same message many times in one transaction, the LISTENer only gets the message once. In other words, a big update won't case a million rebuilds of the static pages. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index
On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote: I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's the error: Please send /srv/pg_upgrade_dump_globals.sql Also, can you restart the old system (by removing the .old suffix, as the message suggests), and then do a SELECT oid,* FROM pg_authid and send the output along? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Picksplit warning
On Tue, 2012-05-08 at 15:11 +0300, Oleg Mürk wrote: Hello, Our postgresql logs are getting filled with warnings: LOG: picksplit method for column COLUMN_IDX of index INDEX_NAME doesn't support secondary split We are using gist indexes on integer, timestamp, and Postgis geometry. Is there a way to work around this problem? http://archives.postgresql.org/pgsql-general/2007-08/msg01810.php A similar complaint from a long time ago. It looks like the conclusion was to demote that to a DEBUG1 message, which won't clutter your logs. It doesn't indicate a real problem. It's essentially saying that PostGIS is missing out on a potential optimization, which is not something you can easily fix. It's also not very well documented, unfortunately, so it's not something the PostGIS folks can fix easily, either. I brought this up on -hackers, so hopefully it will be resolved. In the meantime, you're stuck with the messages cluttering your logfiles. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replication Error
On Mon, 2012-04-30 at 17:23 -0400, Andrew Hannon wrote: 1. Is our data intact? PG eventually starts up, and it seems like once the streaming suffers the FATAL error, it falls back to performing log restores. I don't see anything alarming there. Postgres will not start up if it thinks it's really missing data. I'd advise using an archive command that does not output anything unless it's something you really need to know. A log file missing from the archive is normal operation for recovery mode, so notices telling you that are just cluttering the log. 2. What triggers this error? Too much time between log recovery, streaming startup and a low wal_keep_segments value (currently 128)? 128 sounds like a high-enough number, so after it catches up fully, it should be plenty. It looks like, while trying to catch up, it falls within the 128 segments and begins streaming, and then momentarily falls back out and needs to restore from the archive. Unless you have steady-state replication lag, it should catch up fully and then just be able to use streaming all the time. Do you see it resume streaming later on in the logfile? Disclaimer: I'm not 100% confident in my response, so please take it with a grain of salt, but I hope it is helpful anyway. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updateable Views or Synonyms.
On Wed, 2012-05-30 at 11:16 +1200, Tim Uckun wrote: I am wondering if either of these features are on the plate for postgres anytime soon? I see conversations going back to 2007 on updateable views and some conversations about synonyms but obviously they have never been added to the database for some reason or another. Neither of these has active development right now, as far as I know. Updatable views will appear sometime, I'm sure, but I don't know when. Synonyms sound fairly simple, but I believe there are some concerns around catalog bloat and catalog lookup time. I can't remember the details. With regards to synonyms. It seems to me I could kind of achieve the same functionality by creating a dblink into the same database. Would that be an insane? You'd be working outside of the transaction, so it seems like you're losing a lot there. How does it help you? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Escaping `psql --variable`
On Tue, 2012-05-29 at 18:32 -0400, Alan Gutierrez wrote: Surprised that this works: echo :foo | psql --variable foo=SELECT 1 AS FOO; template1 Why doesn't `psql` escape parameters passed in through `--variable`. When I use a library in other languages, they will escape the variable. How do I use `psql` from `bash` so that it will escape variables and thwart SQL injection? http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-VARIABLES In particular, look at the section on SQL Interpolation. Hopefully that answers your question. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcefully adding a CHECK constrained
On Sat, 2012-05-26 at 22:06 +0300, Catalin(ux) M. Boie wrote: Hello. Thanks for the answer. I really want to avoid reading the whole table. It is too expensive, and with the proposed feature will be not needed. I think is much faster to forcefully add the check if you know the range of data. What do you think? Why not just create the CHECK constraint as NOT VALID, and never validate it? It will still enforce the constraint, it just won't validate it against your old data, which sounds like what you want. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcefully adding a CHECK constrained
On Tue, 2012-05-15 at 12:52 +0300, Catalin(ux) M. BOIE wrote: The old_stats is so big that I cannot afford to add a check constraint. But, I know that all values of the itime field are before 2012_04, so, would be great if I could run something like: ALTER TABLE old_stats ADD CONSTRAINT xxx CHECK (itime 2012_04_timestamp) FORCE; I never looked at PostgreSQL sources, but the commit Enable CHECK constraints to be declared NOT VALID http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=897795240cfaaed724af2f53ed2c50c9862f951f inspired me to dive. Is PostgreSQL's team willing to accept such a feature? It looks like you already found the answer! Create the constraint using NOT VALID, and then sometime later (when you can afford the full scan) do a VALIDATE CONSTRAINT. Unfortunately, this is only available in 9.2, which is still in beta. http://www.postgresql.org/docs/9.2/static/sql-altertable.html CHECK constraints don't use indexes, so CREATE INDEX CONCURRENTLY doesn't help you. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preventing an 'after' trigger from causing rollback on error
On Fri, 2012-04-13 at 17:58 -0400, Eliot Gable wrote: Is there any way I can stop a trigger which fires after a row is inserted into a table from causing a rollback of the entire transaction if something goes wrong? 1. Try using subtransactions ( http://www.postgresql.org/docs/9.1/static/sql-savepoint.html ). I suggest releasing or rolling back the savepoints that you no longer need because triggers can be executed many times. 2. You could have a separate connection that does the processing you need, and use LISTEN/NOTIFY to alert the other connection that new data is available to process. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] efficient trigger function selection?
On Tue, 2012-04-10 at 16:07 -0400, Kenneth Tilton wrote: Suppose I have an RDF-style table (with columns for subject, predicate, various object types, and graph) and want to have dozens or even hundreds of trigger functions defined conditionally on the predicate, ie when predicate = 'your predicate here'. My guess is Postgres is quite efficient at determining which if any trigger functions to call, but I thought I'd ask. I recommend measuring the overhead with some bogus no-op triggers; my guess is that it will be significant but maybe not too bad depending on what the rest of the application is doing. What are you trying to accomplish with so many triggers? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trigger when clause
On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote: Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? It's done with a linear scan of all triggers, testing the WHEN clause for each. To make this a little more concrete, what is likely to perform better a) A single trigger with n if/else clauses b) A set of n triggers each using a different when clause. Both are essentially linear. If you want to scale to a large number of conditions, I would recommend using one trigger in a fast procedural language, and searching for the matching conditions using something better than a linear search. To beat a linear search, you need something resembling an index, which is dependent on the types of conditions. For instance, if your conditions are: 00 = x 10 10 = x 20 20 = x 30 ... you can use a tree structure. But, obviously, postgres won't know enough about the conditions to know that a tree structure is appropriate from a given sequence of WHEN clauses. So, you should use one trigger and code the condition matching yourself. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Resize numeric column without changing data?
On Mon, 2012-04-09 at 16:06 -0400, Lukas Eklund wrote: Ah. I must have not noticed that the typmod for views is not inherited automatically. I'm okay with developing a script to recreate the 15 or so views the depend on that table. What I'm trying to avoid is locking that table for a substantial amount of time. Thanks for the advice! FYI, later versions of postgres try to avoid rewrites of the table when possible for simple ALTERs like the one you're talking about. Are you using PostgreSQL 9.1? Try it out in a simple test case... maybe the lock is only held for an instant anyway. Some of these optimizations went in 9.2 (not released yet) but I think the one you need is in 9.1. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
On Sat, 2012-03-31 at 13:21 +0200, Janning Vygen wrote: The OS was installed a few days before, the i installed the postgresql instance. I configured my setup with a backup server by WAL archiving. Then i tested some things and i played around with pg_reorg (but i didn't use ist till then) then i dropped the database, shut down my app, installed a fresh dump and restarted the app. Hmm... I wonder if pg_reorg could be responsible for your problem? I know it does a few tricky internal things. Is it still worth to make the copy now? At the moment everything is running fine. Probably not very useful now. No, i didn't found any in my postgresql dirs. Should i have a core file around when i see a segmentation fault? What should i look for? It's an OS setup thing, but generally a crash will generate a core file if it is allowed to. Use ulimit -c unlimited on linux in the shell that starts postgresql and I think that will work. You can test it by manually doing a kill -11 on the pid of a backend process. I have never done it before. But as everything runs fine at the moment it's quite useless, isn't it? I meant a backtrace from the core file. If you don't have a core file, then you won't have this information. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
On Fri, 2012-03-30 at 16:02 +0200, Janning Vygen wrote: The PANIC occurred first on March, 19. My servers uptime ist 56 days, so about 4th of February. There was no power failure since i started to use this machine. This machine is in use since March, 7. I checked it twice: Now power failure. Just to be sure: the postgres instance didn't exist before you started to use it, right? Did you get the PANIC and WARNINGs on the primary or the replica? It might be worth doing some comparisons between the two systems. It only happend on my primary server. My backup server has no suspicious log entries. Do you have a full copy of the two data directories? It might be worth exploring the differences there, but that could be a tedious process. It is pretty obvious to me the segmentation fault is the main reason for getting the PANIC afterwards. What can cause a segmentation fault? Is there anything to analyse further? It's clear that they are connected, but it's not clear that it was the cause. To speculate: it might be that disk corruption caused the segfault as well as the PANICs. Do you have any core files? Can you get backtraces? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PSQL 9.1.3 segmentation fault
On Fri, 2012-03-30 at 20:11 +, Hu, William wrote: I used –with-openssl option with the configure, after starting the server, ... Psql would cause a segmentation fault, createuser did too. Can you try with plain ./configure --prefix=/your/install/path and see if there is still a problem? It might be a problem related to openssl. If it is a problem with openssl, try to figure out if the library matches the headers. You should be able to see what's happening during make when it's linking the psql or createuser binaries. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PANIC: corrupted item pointer
Hi, First of all, shut down both servers (you indicated that you have a replica) and make a full copy of both data directories. At the first sign of corruption, that's always a good step as long as it's a practical amount of data (obviously this is more of a challenge if you have terabytes of data). On Tue, 2012-03-27 at 11:47 +0200, Janning Vygen wrote: Hi, I am running postgresql-9.1 from debian backport package fsync=on full_page_writes=off That may be unsafe (and usually is) depending on your I/O system and filesystem. However, because you didn't have any power failures, I don't think this is the cause of the problem. I didn't had any power failures on this server. These WARNINGs below could also be caused by a power failure. Can you verify that no power failure occurred? E.g. check uptime, and maybe look at a few logfiles? Now I got this: 1. Logfile PANIC postgres[27352]: [4-1] PANIC: corrupted item pointer: offset = 21248, size = 16 ... Then I run VACUUM rankingentry and i got: kicktipp=# VACUUM rankingentry ; WARNING: relation rankingentry page 424147 is uninitialized --- fixing WARNING: relation rankingentry page 424154 is uninitialized --- fixing WARNING: relation rankingentry page 424155 is uninitialized --- fixing WARNING: relation rankingentry page 424166 is uninitialized --- fixing WARNING: relation rankingentry page 424167 is uninitialized --- fixing WARNING: relation rankingentry page 424180 is uninitialized --- fixing VACUUM Time: 138736.347 ms ... I am worried because i never had any error like this with postgresql. I just switched to 9.1 and started to have a hot standby server (WAL shipping). Does this error has any relation to this? Did you get the PANIC and WARNINGs on the primary or the replica? It might be worth doing some comparisons between the two systems. Again, make those copies first, so you have some room to explore to find out what happened. It seems very unlikely that problems on the master would be caused by the presence of a replication slave. Should I check or exchange my hardware? Is it a hardware problem? It could be. Should I still worry about it? Yes. The WARNINGs might be harmless if it were a power failure, but you say you didn't have a power failure. The PANIC is pretty clearly indicating corruption. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade + streaming replication ?
On Wed, 2012-03-21 at 09:12 +0100, Henk Bronk wrote: On linux, you can also do a cp -rpuv. source destination My point was that we should not take shortcuts that avoid the work of a full base backup for the replicas until we've determined a safe way to do that. As far as I know, nobody has successfully and safely done a pg_upgrade of a set of replicas without the need for full base backups (which need to copy all of the user data). Until someone determines that it's safe, adds the necessary functionality to pg_upgrade and/or replication, and documents it; then I do _not_ recommend such a thing for any production system. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade + streaming replication ?
On Tue, 2012-03-20 at 16:49 -0400, Bruce Momjian wrote: On Tue, Mar 20, 2012 at 02:58:20PM -0400, Bruce Momjian wrote: On Tue, Mar 20, 2012 at 11:56:29AM -0700, Lonni J Friedman wrote: So how can you resume streaming without rebuilding the slaves? Oh, wow, I never thought of the fact that the system tables will be different? I guess you could assume the pg_dump restore is going to create things exactly the same on all the systems, but I never tested that. Do the system id's have to match? That would be a problem because you are initdb'ing on each server. OK, crazy idea, but I wonder if you could initdb on the master, then copy that to the slaves, then run pg_upgrade on each of them. Obviously this needs some testing. This sounds promising. Fundamentally, the user data files aren't changing, and if you can upgrade the master you can upgrade the slaves. So there is no fundamental problem here, but there will be some careful bookkeeping. I think we need to look at this as a new feature that needs its own testing and documentation. It's important though, because as you point out downthread, rsync doesn't really solve the problem (still takes time proportional to the user data size). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade + streaming replication ?
On Tue, 2012-03-20 at 22:21 +0100, Henk Bronk wrote: actually rsync works fine on file level and is good for manual syncing. it check really the files with the stat command, so a bit change will trigger the copy in practice you need to keep an eye on compleetness of the rsync action. Rsync still needs to examine the entire file. It has no information to know that the file is the same on master and slave. We could try to give it the appropriate information on which it can make that assumption -- e.g. keep the timestamps the same so that rsync assumes the contents are the same. But that seems fragile and I don't see a good way of doing it, anyway. We need a way to take a base backup of just the catalogs, essentially, and leave the user data intact. Probably quite a few details to sort out though. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade + streaming replication ?
On Mon, 2012-03-19 at 15:30 -0400, Bruce Momjian wrote: On Thu, Mar 01, 2012 at 02:01:31PM -0800, Lonni J Friedman wrote: I've got a 3 node cluster (1 master/2 slaves) running 9.0.x with streaming replication. I'm in the planning stages of upgrading to 9.1.x, and am looking into the most efficient way to do the upgrade with the goal of minimizing downtime risk. After googling, the only discussion that I've found of using pg_upgrade with a streaming replication setup seems to be this (nearly) year old thread: http://web.archiveorange.com/archive/v/9FNVlDWGQtpyWVL54jlK In summary, there is no way to use both pg_upgrade and streaming replication simultaneously. I'd have to either use pg_upgrade and then effectively rebuild/redeploy the slaves, or not use pg_upgrade, and reimport all of the data. Is that still the latest status, or are there other options? You can shut down all three servers, run pg_upgrade on all of them, then restart them as 9.1 servers. After running pg_upgrade on each server individually, they will have different system IDs, and potentially different on-disk representation of the catalogs, right? So how can you resume streaming without rebuilding the slaves? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temporal foreign keys
On Fri, 2012-03-16 at 15:13 +0100, Andreas Kretschmer wrote:
On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
how can I implement temporal foreign keys with postgresql? Is writing
triggers the only way to enforce temporal referential integrity
currently?
It works in 9.2devel ;-)
test=# create table x (d daterange primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index x_pkey
for table x
CREATE TABLE
test=*# create table y (d daterange references x);
CREATE TABLE
test=*# insert into x values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-20)');
ERROR: insert or update on table y violates foreign key constraint
y_d_fkey
DETAIL: Key (d)=([2012-01-01,2012-01-20)) is not present in table x.
If I understand what he was asking for, it was a kind of range foreign
key which means that the following query should succeed:
insert into y values ('[2012-01-02,2012-01-04)');
because that range is contained in a value in the table x.
So it's slightly different semantics than a normal foreign key.
But yes, normal foreign keys (based on equality) work fine over range
types.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing MS/Open Office and PDF documents
On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com wrote: Hi, We are looking to use Postgres 9 for the document storing and would like to take advantage of the full text search capabilities. We have hard time identifying MS/Open Office and PDF parsers to index stored documents and make them available for text searching. Any advice would be appreciated. The first step is to find a library that can parse such documents, or convert them to a format that can be parsed. After you do that, PostgreSQL allows you to load arbitrary code as functions (in various languages), so that will allow you to make use of the library. It's hard to give more specific advice until you've found the library you'd like to work with. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temporal foreign keys
On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote: Hey, how can I implement temporal foreign keys with postgresql? Is writing triggers the only way to enforce temporal referential integrity currently? Yes, currently that's the only way. Look at CREATE CONSTRAINT TRIGGER. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xlog corruption
On Mon, 2012-02-27 at 16:30 -0800, Jameison Martin wrote: I'd like to get some clarification around an architectural point about recovery. I see that it is normal to see unexpected pageaddr errors during recovery because of the way Postgres overwrites old log files, and thus this is taken to be a normal termination condition, i.e. the end of the log (see http://doxygen.postgresql.org/xlog_8c.html#a0519e464bfaa79bde3e241e6cff986c7). My question is how does recovery distinguish between the actual end of the log as opposed to a log file corruption (e.g. torn page)? I'd like to be able to distinguish between a corruption in the log vs. a normal recovery condition if possible. If you have a power failure, a torn page in the WAL is expected. Torn pages in the data pages are fixed up using WAL; but WAL doesn't have anything under it to prevent/fix torn pages (unless your filesystem prevents them). Of course, checksums are used to prevent recovery from attempting to play a partial or otherwise corrupt WAL record. What kind of corruption are you trying to detect? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is the life of a postgres back end process?
On Mon, 2012-02-13 at 17:30 -0500, Eliot Gable wrote: Are postgres back end processes connection specific? In other words, can we assume / trust that they will be terminated and cleaned up when we close a connection and that they will not live on and be reused by other connections? Yes, one backend per connection. When you close the connection, the backend process should go away. Under some circumstances, that might not always happen immediately if the backend is in the middle of doing some work. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select where not exists returning multiple rows?
On Tue, 2011-11-01 at 10:59 -0400, Chris Dumoulin wrote: Indexes: item_pkey PRIMARY KEY, btree (sig) And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. Unrelated note: just use $1 twice. The idea is to insert if the row doesn't already exist. We're getting primary key constraint violations: What's happening is that the NOT EXISTS is running before the INSERT, and between those two another INSERT can happen. The PRIMARY KEY is saving you from this problem in this case. I recommend that you look into using SERIALIZABLE isolation mode as your default: http://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html http://www.postgresql.org/docs/9.1/static/transaction-iso.html#XACT-SERIALIZABLE That will still throw an error, but it protects you from all kinds of similar problems that might not be caught by a primary key. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump -n switch lock schema from dml/ddl?
On Thu, 2011-11-10 at 11:46 -0500, Tony Capobianco wrote: I'm testing out various pg_dump scenarios using the -n switch and I have a few questions: - When using the -n switch, is the whole schema locked from all non-read DML/DDL operations? No. - Once the final table is dumped (i.e. pg_dump: dumping contents of table zip_data), are there additional background processes that are still performing maintenance tasks? Or is the entire process complete and all objects are released? No background work results from using pg_dump. I'm asking because I have a schema with a large table with many indexes that is consuming the majority of the dump. This version of the dump takes about 4 hours. As a solution, we run 2 separate dumps in parallel, one with the schema excluding the large table and one including only the large table. FYI: you need to be a little careful running two pg_dumps in parallel. It may (though not necessarily) increase the speed, but it also means that you get different snapshots for the big table and all the rest of the data. Ordinarily, you only want on snapshot so that it's a single point-in-time for all of the dumped data. Otherwise, you may have inconsistent data. The option with just the large table takes 2.5 hours. However, the option with the schema excluding the large table still takes 4 hours. If pg_dump locks each table individually, then releases when the dump is completed, I must be encountering lock contention. Also, I use the -v switch, however I'm not getting any information on how long the dump of each object takes, is there an option that exists where I can collect this information in the log file? If you'd like to know what's happening on your system, the best way is to start out with (while the pg_dumps are running): SELECT * FROM pg_stat_activity; in a separate client connection. If the waiting flag is true on one query for a significant amount of time, it may be lock-related. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timeline X of the primary does not match recovery target timeline Y
On Mon, 2011-09-26 at 08:54 -0400, Adam Tistler wrote: I am trying to set up master/slave warm standby streaming replication (9.1). I am not doing archiving/log shipping. I have read that you cannot reverse roles between the master and slave, and that once a slave is promoted, you need to use pg_basebackup or rsync to copy files from the newly promoted master to the old master. I am fine with this, however, in my case all I am trying to do is re-enable the slave to be in recovery mode. I am doing this by deleting the trigger file ( which I had originally created to promote the slave to master ) and moving recovery.done to recovery.conf, then restarting postgres. As a result I get the following error: FATAL: timeline 2 of the primary does not match recovery target timeline 3 Once it's promoted to a primary, you can't set it to start recovering from another system again (without taking a new base backup). Did I understand your question correctly? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timeline X of the primary does not match recovery target timeline Y
On Thu, 2011-10-20 at 12:45 -0400, Adam Tistler wrote: Yes you understood correctly. Would the same apply for a hot standby situation? Right. Hot standby just means that you can query (read-only) an instance that's still in recovery. Basically, going from recovery mode (or hot standby, which is also recovery mode) to up (that is, can accept write queries and operate normally) is not a reversible process. You have to make a new base backup of another system to start recovering again. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting bytea using PHPs pg_escape_bytea()
On Thu, 2011-10-20 at 14:13 -0300, Martín Marqués wrote: How would that work with abstraction layers like MDB2 or PDO? I'm not sure. If there isn't some way to use parameterized queries, then it's not a very good abstraction layer, in my opinion (because parameterized queries are widely recognized as a good idea). Sometimes it is tied to the mechanism for preparing a query -- you might try that. The only place I get these messages are when inserting (or updateing) bytea columns with images (normally jpeg and png). That's probably because normal strings aren't as likely to use escape sequences. But binary data pretty much needs to, so it does octal escapes (or is it hex now?), like: \000 for a zero byte. However, because the non-standard string literals allow for backslash escapes as well, it ends up looking like (for standard_conforming_strings=FALSE): '\\000' after escaping the bytea and escaping it to be a string literal. When standard_conforming_strings is on, then backslash is no longer a special character in string literals, so it can just do the bytea escaping and that's it, so the zero byte as a string literal would look like: '\000' or perhaps: '\x00' I hope this helps. My advice is to just try it in different ways and see what strings are sent to postgresql (by setting log_statement_min_duration=0, which will log all the SQL). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting bytea using PHPs pg_escape_bytea()
On Wed, 2011-10-19 at 14:30 -0300, Martín Marqués wrote: The only concern I have is that on insertion, I get this WARNING: WARNING: nonstandard use of \\ in a string literal at character 41 HINT: Use the escape string syntax for backslashes, e.g., E'\\'. Should I worry? What does it mean? First of all, the best solution is to use parameterized queries: http://us.php.net/manual/en/function.pg-query-params.php But here's the explanation for the warning: Check the settings for: SHOW standard_conforming_strings; SHOW escape_string_warning; I assume that those are false and true respectively. If that's the case, you are safe, HOWEVER it means that you are using non-standard literals. It's advisable to move to standard string literals (that is, as the SQL spec defines them) because if you port your application to other systems in the future, or if you later turn standard_conforming_strings to TRUE, then you could be vulnerable to SQL injection. To become standards-compliant, set standard_conforming_strings to TRUE, and pg_escape_bytea should automatically start working in the standard way. It is advisable to explicitly pass the connection object (first parameter) to pg_escape_bytea() to make sure no mistakes are made. Try it out with a few test strings to make sure it's using the correct escaping, see: http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS Another option is to continue to use the C-style escaping, which you can do by prefixing the literal with an E (as described in the document above). I know this all sounds fairly complicated. Essentially, postgresql adopted a non-standard literal syntax a long time ago, and has been trying to move away from that slowly for a long time. In the end, matching the standard syntax should be a net win against SQL injection (as well as making porting easier). I hope this helps. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
On Wed, 2011-10-05 at 15:35 +0200, thomas veymont wrote: hello, let's say that each rows in a table contains a start time and a end time (timeinterval type), but the index are not ordered nor consecutive, e.g : I think your question has already been answered, but I thought you might be interested in: Period data type: http://pgxn.org/dist/temporal/ Or Exclusion Constraints, which can prevent overlapping ranges: http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE Also, I'm currently working on a feature called Range Types, which will hopefully be in 9.2. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is 9.1 considered more stable/robust than 9.0.4 ?
On Thu, 2011-09-22 at 02:36 -0500, Abraham, Danny wrote: Thanks Danny If you are considering migrating from 9.0.X to 9.1.Y, then perhaps wait for a couple patch releases before going into production. However, I highly recommend that you migrate your development environment now, at least as a test. That will give you advance warning of any problems. However, if you are developing a new application, you might as well develop against 9.1, because it will stabilize while you develop and do your own testing. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disconnecting and cancelling a statement
On Wed, 2011-09-07 at 14:46 +0800, Craig Ringer wrote: Right now, PostgreSQL doesn't seem to make an effort to detect a client cancellation. For instance, if you do a select pg_sleep(1000) and then kill -9 the client, the SELECT will remain running. pg_sleep isn't a good test. In fact, Pg _does_ make an effort to detect when a client dies, and will try to terminate the query. It does this via explicit checks at various points, none of which are reached while Pg is idling in a sleep() syscall. During more typical query processing you'll usually find that a query gets terminated when the client dies. pg_sleep is not merely a wrapper around the sleep system call, it does call CHECK_FOR_INTERRUPTS() periodically. Also, you can see that pg_sleep can be easily canceled if the signal arrives while the query is actually running (try in psql, or try removing the SIGSTOP/SIGCONT signals from the C code I attached to the first message). Try with a large cartesian product and you should get the same problem. Pg must find out when the client dies, though. If the client just goes away - such as with a laptop on wifi that wanders out of range - it won't know about it until it next attempts to send data to the client. How does it know, even on a good network connection, when the client disconnects? I attached a reproducible case, so you should see what I'm talking about. To address this, if you want reliable client dropout detection, you need to enable tcp keepalives and set them to quite aggressive so the OS will periodically test the connection for aliveness. I'd be happy if it just detected a disconnect that the OS already knows about, e.g. explicitly closing the socket. I'd love to see Pg accept OOB cancel requests done via lightweight connections that don't go through the whole setup process. It does that for cancel (see PQcancel), but there is no equivalent for termination. If the server sent a statement cookie when executing a statement, the client could hang onto that and use it to issue a cancel for that statement and only that statement by establishing a new connection to the server and sending that cookie rather than the usual negotiation and auth process. There'd be no need to go through full auth or even bother with SSL, because it's a one-time random (or hash-based) code. Pooling systems could send this to _all_ servers, or it could be prefixed with a server identifier that helped poolers route it to the right server. That's not too far from what's already done -- again, see the source for PQcancel() and processCancelRequest(). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disconnecting and cancelling a statement
I'm looking for a reliable way for a client to disconnect from a backend
such that any running query is terminated.
Right now, PostgreSQL doesn't seem to make an effort to detect a client
cancellation. For instance, if you do a select pg_sleep(1000) and then
kill -9 the client, the SELECT will remain running. That's not so much
of a problem for sleep, but if it's doing real work, then it's wasting a
lot of effort (and perhaps not terminating in any reasonable amount of
time).
And even if the client makes an effort to cancel and there are no major
network problems, then I still don't see a good method. Because the
cancellation request is sent out-of-band to the postmaster, then it's in
a race with the (asynchronous) query that you just sent. If the signal
reaches the backend before the query does, then the SIGINT becomes a
no-op (because it's still idle), and then the query arrives, and then
the client does PQfinish, the backend will still be alive doing a bunch
of needless work.
I have attached a simple C program that demonstrates the problem (must
be run from same host as PG because it uses SIGSTOP/SIGCONT to reproduce
race). After you run it, see how the SELECT pg_sleep(1000) is still
running, despite the client being disconnected.
There are two solutions that I see, neither of which look great:
1. Make a separate connection, and issue pg_terminate_backend() before
PQfinish. It works because a SIGTERM will not be a no-op on an idle
backend. This solution requires superuser privileges (not acceptable),
plus it's a little ugly.
2. Keep sending cancellation requests in a loop with a delay, consuming
input each time until PQisBusy() returns false. Obviously fairly ugly
and error prone, but is somewhat acceptable.
Any other ideas? There is no PQterminate, unforunately.
statement_timeout is not feasible, as the statement might legitimately
run for a very long time.
This is all compounded by the fact that terminating the backend directly
is no guarantee of proper shutdown, either:
http://archives.postgresql.org/pgsql-general/2009-03/msg00434.php
That means that there is no way to nicely and reliably shut down
postgresql from the client alone, nor from the server alone. The only
way is to send a SIGTERM to the backend *and* terminate the client
connection. Unless someone has a better idea?
Thoughts?
Regards,
Jeff Davis
/*
* Only works if run on the same host as postgres; can't work over a
* network because we need to be able to signal backend directly.
*/
#include stdlib.h
#include stdio.h
#include libpq-fe.h
#include sys/types.h
#include signal.h
#define ERRBUF_SIZE 256
int main(int argc, char *argv[])
{
char *conninfo;
PGconn *conn;
PGcancel *cancel;
char errbuf[ERRBUF_SIZE];
pid_t be_pid;
if (argc 2)
{
fprintf(stderr, must supply connection string as argument);
exit(1);
}
conninfo = argv[1];
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, Connection to database failed: %s,
PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
cancel = PQgetCancel(conn);
be_pid = PQbackendPID(conn);
printf(sending SIGSTOP to %d\n, be_pid);
kill(be_pid, SIGSTOP);
printf(sending query\n);
PQsendQuery(conn, select pg_sleep(1000));
printf(sending cancel\n);
PQcancel(cancel, errbuf, ERRBUF_SIZE);
printf(sending SIGCONT to %d\n, be_pid);
kill(be_pid, SIGCONT);
printf(disconnecting\n);
PQfreeCancel(cancel);
PQfinish(conn);
return 0;
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I merge two tables?
On Thu, 2011-09-01 at 13:09 -0400, Jerry LeVan wrote: As time goes by the tables on the various computers get out of sync. Is there an elegant way I can get all of the differences (uniquely) merged into a single table? You can try a query involving NOT EXISTS, combined with dblink: http://www.postgresql.org/docs/current/static/dblink.html Effectively the query would be something like: INSERT INTO registrations SELECT * FROM -- fetch remote version of table dblink(..., SELECT * FROM registrations) AS remote_reg(...) WHERE NOT EXISTS (SELECT 1 FROM registrations local_reg WHERE local_reg.id = remote_reg.id); (disclaimer: I didn't test this query out, it's just for illustrating the idea). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database Restore Fail - No liblwgeom.so
On Tue, 2011-07-19 at 14:16 +0100, Rebecca Clarke wrote: Hi there I'm transferring a database from 8.2 to 8.4 and I have some triggers that reference liblwgeom.so within the database. It sounds like you have some triggers that were compiled against one version of PostGIS, and you need to recompile them against the new version of PostGIS. It might be better to ask on the PostGIS mailing list, they might have more context that can help you. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Error calling PG_RETURN_NULL()
[ Moved to pgsql-general. pgsql-hackers is for development of PostgreSQL itself. ] On Mon, 2011-07-25 at 20:06 -0300, Alexandre Savaris wrote: ** Error ** ERRO: input function 49344 returned NULL SQL state: XX000 Character: 45 It seems like the call to PG_RETURN_NULL() on the input function is causing the error. Is this the correct behaviour? There's another way to return a NULL value as the result of a data type's input function? The docs say: The input function must return a value of the data type itself. http://www.postgresql.org/docs/9.0/static/sql-createtype.html Which means you can't return a NULL from the input function when there is non-NULL input. The context around that statement is a little more informative, but perhaps it could be more clear. I assume that postgresql has that requirement because it needs to know whether something is NULL without necessarily knowing what type it is. For instance: ' ' IS NULL Should that be true or false? If it depends on the type of the left-hand-side, how do you figure out what type it is? I'm not sure if this is the exact reason it's prohibited, but it seems like there would be a problem somewhere along these lines. Interesting idea though. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SerializableSnapshot removed from postgresql 8.4
On Wed, 2011-07-13 at 18:10 +0100, Duarte Fonseca wrote: Hi list, I'm currently upgrading from Postgresql 8.1 to 8.4 one of the steps of the process for me involves compiling the replication toolkit we use against 8.4. I've just run into a problem since this replication code references SerializableSnapshot which as been removed in 8.4, i was wondering what should our code use instead, I found a thread[1] in the hackers mailing list where GetActiveSnapshot() was recommended, i would greatly appreciate it if someone could point me in the right direction on this. The code in question goes something like: if (SerializableSnapshot == NULL) elog(ERROR, SerializableSnapshot is NULL ); // Return the minxid from the current snapshot PG_RETURN_TRANSACTIONID(SerializableSnapshot-xmin); I believe that equivalent code in 8.4 would look something like: if (!IsXactIsoLevelSerializable || !ActiveSnapshotSet()) elog(ERROR, Could not find serializable snapshot); PG_RETURN_TRANSACTIONID(GetActiveSnpashot()-xmin); However, be careful! Some of this code changes again in 9.1. In 9.1, you probably want to look for the repeatable read transaction. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accidentally truncated pg_type
On Tue, 2011-07-12 at 01:12 +0100, Matthew Byrne wrote: I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? [ Only consider this after you've taken Craig's advice. ] Did you have any user-defined types or extensions? You might try something as simple as (on your throw-away experimental copy, of course): 1. Make a new cluster with initdb (or just connect to a different database, if that still works). 2. Load any extensions or user-defined types into that one, and make sure they get the same OIDs (or hack the output of the next step). 3. Copy out the contents of pg_type, including OIDs. 4. Copy that data back into your empty pg_type. 5. Try to do a logical backup, load that data into a fresh instance, and you might be OK. I haven't really thought this plan through, but that's the first thing I'd try (after doing file-level copies of everything, of course!). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 12:34 -0700, Darren Duncan wrote: Yes, but that would just be in-memory or in temporary places external to every database. On disk internal to a database there would just be the oid. In fact, another aspect of the database model I defined is that each database is entirely self-contained; while you can do cross-database queries, you don't have cross-database constraints, in the general case. Yes, you can have a local oid and a fully-qualified oid. It sounds like it might take some effort (which is an understatement) to go through the system and figure out which ones should be local and which ones should be fully-qualified. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 21:04 -0700, Darren Duncan wrote: I think you should make more of an effort to understand how the system works now, and why, before proposing radical redesigns. Well yes, of course. But that will take time and I think I already understand enough about it to make some useful contributions in the meantime. How much or what I already know may not always come across well. If this bothers people then I can make more of an effort to reduce my input until I have more solid things to back them up. I don't think anyone expects you to understand all the internal APIs in postgres before you make a proposal. But we do expect you to look critically at your own proposals with the status quo (i.e. existing code, users, and standards) in mind. And that probably means poking at the code a little to see if you find stumbling blocks, and asking questions to try to trace out the shape of the project. I'm hoping that we can learn a lot from your work on Muldis D. In particular, the type system might be the most fertile ground -- you've clearly done some interesting things there, and I think we've felt some pressure to improve the type system from a number of different projects*. Regards, Jeff Davis * That being said, PostgreSQL's type system is actually very good. Consider the sophisticated type infrastructure (or at least plumbing around the type system) required to make KNN-GiST work, for instance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Fri, 2011-07-08 at 23:39 -0700, Darren Duncan wrote: What if you used the context of the calling code and resolve in favor of whatever match is closest to it? The problem is related to general-purpose programming languages. Basically start looking in the lexical context for an x and if you find one use that; otherwise, assuming we're talking about referencing code that lives in the database such as a function, look at the innermost schema containing the referencing code and see if it has a direct child named x; otherwise go up one level to a parent schema, and so on until you get to the top, and finding none by then say it doesn't exist. This is an example of where data languages and normal programming languages have a crucial difference. With a data language, you have this problem: 1. An application uses a query referencing 'y.z.foo' that resolves to internal object with fully-qualified name 'x.y.z'. 2. An administrator creates object 'y.z.foo'. Now, the application breaks all of a sudden. In a normal prgramming language, if the schema of the two foos are different, the compiler could probably catch the error. SQL really has no hope of catching it though. PostgreSQL has this problem now in a couple ways, but it's much easier to grasp what you might be conflicting with. If you have multiple nested levels to traverse and different queries using different levels of qualification, it gets a little more messy and I think a mistake is more likely. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan wrote: I think an even better way to support this is would be based on Postgres having support for directly using multiple databases within the same SQL session at once, as if namespaces were another level deep, the first level being the databases, the second level the schemas, and the third level the schema objects. Kind of like what the SQL standard defines its catalog/schema/object namespaces. This instead of needing to use federating or that contrib module to use multiple Pg databases of the same cluster at once. Under this scenario, we make the property of a database being read-only or read-write for the current SQL session associated with a database rather than the whole SQL session. A given transaction can read from any database but can only make changes to the ones not read-only. Also, the proper way to do temporary tables would be to put them in another database than the main one, where the whole other database has the property of being temporary. Under this scenario, there would be separate system catalogs for each database, and so the ones for read-only databases are read-only, and the ones for other databases aren't. Then the system catalog itself fundamentally isn't more complicated, per database, and anything extra to handle cross-database queries or whatever, if anything, is a separate layer. Code that only deals with a single database at once would be an optimized situation and perform no worse than it does now. One challenge that jumps to mind here is that an Oid would need to become a pair (catalog, oid). Even if the end result isn't much more complex, getting there is not trivial. See also how SQLite works; this mount being analogous to their attach. I'm not sure SQLite is the best example. It has a radically different architecture. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Latency problems with simple queries
On Thu, 2011-07-07 at 12:13 +0100, Adrian Schreyer wrote: I randomly get latency/performance problems even with very simple queries, for example fetching a row by primary key from a small table. Since I could not trace it back to specific queries, I decided to give LatencyTOP (http://www.latencytop.org/) a go. Soon after running a couple of queries, I saw this in latencytop whilst a query was hanging in postgres: Cause Maximum Percentage Writing a page to disk19283.9 msec99.7 What IO scheduler and filesystem are you using? I think that CFQ has some problems for database workloads. It would be easy to test: just switch to deadline and/or noop for a while and see if the problem persists. Also, I have heard of a few strange things with ext4, but they have probably fixed those issues and it would be much harder for you to test. But it might be worth searching for issues/bugs with your particular version of the filesystem. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And no, there is no workaround. That sounds like a deficiency to overcome. It should be possible for those system catalogs to be virtual, defined like union views over similar immutable tables for the read-only database plus mutable in-memory ones for the temporary tables. Ideally, yes, from a logical standpoint there are catalog entries that are only interesting to one backend. But that doesn't mean it's easy to do. Remember that catalog lookups (even though most go through a cache) are a path that is important to performance. Also, more complex catalog interpretations may introduce some extra bootstrapping challenges. Are there any plans in the works to do this? I don't think so. It sounds like some fairly major work for a comparatively minor benefit. Suggestions welcome, of course, to either make the work look more minor or the benefits look more major ;) Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] point types in DISTINCT queries
On Wed, 2011-06-29 at 11:37 -0400, Jonathan S. Katz wrote: Which means it *should* work, but first I would need to clean up the data and find the duplicates. I was hoping this might work: SELECT geocode, count(*) FROM a GROUP BY a.geocode HAVING count(*) 1; Maybe you could use a self-join as a workaround for now, just to clean up the data? SELECT geocode, other_columns from a a1, a a2 where a1.other_columns a2.other_columns and a1.geocode ~= a2.geocode; Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] point types in DISTINCT queries
On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php However I wanted to see if it was still necessary that I would need the complete btree operator class to run such a query. Yes, the default btree operator class is used to find the equality operator. Even though you have defined the operator =, postgresql doesn't rely on that meaning equals -- the btree operator class is what imparts that meaning. Are there plans to have a defined = operator on the point type? I can understand how the other geometric types, = would represent area, but AFAIK I think = could be safely applied on a point type (and i realize I could submit a patch for that :-) maybe depending on the resolution to this / refreshing my C...). The built-in geometric types haven't received a lot of attention lately. Most people who use geometric data use the PostGIS extension, which is a sophisticated extension that can deal with that kind of data. You might want to check that out and see if it meets your needs. Perhaps someone is interested in bringing the built-in geometric types up to speed; but I think most of the interest is moving things like this out to extensions where they can be more easily be maintained by interested parties. If you'd like to submit a patch, I suggest first asking on -hackers whether improvements to the built-in spatial types would be accepted. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [HACKERS] Postmaster holding unlinked files for pg_largeobject table
[ For future reference, -general is the appropriate list. Moving discussion there. ] On Sat, 2011-06-04 at 00:45 +0300, Alexander Shulgin wrote: We've noticed that free disk space went down heavily on a system, and after a short analysis determined that the reason was that postmaster was holding lots of unlinked files open. A sample of lsof output was something like this: ... Restarting PostgreSQL obviously helps the issue and the disk space occupied by those unlinked files (about 63GB actually) is reclaimed. Normally postgres closes unlinked files during a checkpoint. How long between checkpoints on this system? Is it possible that you noticed before postgresql caused an automatic checkpoint? Also, you can do a manual checkpoint with the CHECKPOINT command. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] full_page_writes makes no difference?
On Wed, 2011-05-04 at 00:17 -0400, Tian Luo wrote: So, nbytes should always be multiples of XLOG_BLCKSZ, which in the default case, is 8192. My question is, if it always writes full pages no matter full_page_writes is on or off, what is the difference? Most I/O systems and filesystems can end up writing part of a page (in this case, 8192 bytes) in the event of a power failure, which is called a torn page. That can cause problems for postgresql, because the page will be a mix of old and new data, which is corrupt. The solution is full page writes, which means that when a data page is modified for the first time after a checkpoint, it logs the entire contents of the page (except the free space) to WAL, and can use that as a starting point during recovery. This results in extra WAL data for safety, but it's unnecessary if your filesytem + IO system guarantee that there will be no torn pages (and that's the only safe time to turn it off). So, to answer your question, the difference is that full_page_writes=off means less total WAL data, which means fewer 8192-byte writes in the long run (you have to test long enough to go through a checkpoint to see this difference, however). PostgreSQL will never issue write() calls with 17 bytes, or some other odd number, regardless of the full_page_writes setting. I can see how the name is slightly misleading, but it has to do with whether to write this extra information to WAL (where extra information happens to be full data pages in this case); not whether to write the WAL itself in full pages. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Mon, 2011-05-02 at 20:06 -0600, Rob Sargent wrote: Jeff Davis wrote: In particular, I think you are falsely assuming that a natural key must be generated from an outside source (or some source outside of your control), and is therefore not reliably unique. You can generate your own keys... ... My wife works (at the sql level) with shall we say records about people. Real records, real people. Somewhere around 2 million unique individuals, several million source records. They don't all have ssn, they don't all have a drivers license. They don't all have an address, many have several addresses (especially over time) and separate people have at one time or another lived at the same address. You would be surprise how many bob smiths where born on the same day. But then they weren't all born in a hospital etc etc etc. A person may present on any of a birth record, a death record, a hospital record, a drivers license, a medical registry, a marriage record and so on. There simply is no natural key for a human. We won't even worry about the non-uniqueness of ssn. And please don't get her started on twins. :) I can only imagine that other equally complex entities are just as slippery when it comes time to pinpoint the natural key. I think you missed my point. You don't have to rely on natural keys that come from somewhere else; you can make up your own, truly unique identifier. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Tue, 2011-05-03 at 13:35 -0600, Rob Sargent wrote: Sorry, but I'm confused, but that's common. Isn't a natural key to be compose solely from the attributes of the entity? As in a subset of the columns of the table in a third-normalish world. Isn't tacking on another column with a concocted id joining the pervassiveness? Not in my opinion. Before cars existed, there was no driver's license number. The DMV (as it's called in California, anyway) created it, and it's now a key that they can trust to be unique. It's also an attribute of the entity now, because it's printed on the cards you hand to people. The thing that I think is a mistake is to use generated IDs like an internal implementation detail (i.e. hide them like pointers); then at the same time mix them into the data model. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Mon, 2011-05-02 at 23:07 -0400, Greg Smith wrote: I see this whole area as being similar to SQL injection. The same way that you just can't trust data input by the user to ever be secure, you can't trust inputs to your database will ever be unique in the way you expect them to be. So, don't trust them to be unique then. Make up your own unique identifier, and use that. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote: The position Merlin has advocated here, that there should always be a natural key available if you know the data well enough, may be true. But few people are good enough designers to be sure they've made the decision correctly, and the downsides of being wrong can be a long, painful conversion process. Easier for most people to just eliminate the possibility of making a mistake by using auto-generated surrogate keys, where the primary problem you'll run into is merely using more space/resources than you might otherwise need to have. It minimizes the worst-case--mistake make in the model, expensive re-design--by adding overhead that makes the average case more expensive. Once you really try to define natural and surrogate keys, I think a lot of the arguments disappear. I wrote about this a few years back: http://thoughts.j-davis.com/2007/12/11/terminology-confusion/ In particular, I think you are falsely assuming that a natural key must be generated from an outside source (or some source outside of your control), and is therefore not reliably unique. You can generate your own keys, and if you hand them out to customers and include them on paperwork, they are now a part of the reality that your database models -- and therefore become natural keys. Invoice numbers, driver's license numbers, etc., are all natural keys, because they are known about, and used, in reality. Usernames are, too, the only difference is that you let the user choose it. In contrast, a pointer or a UUID typically does not represent reality, because no humans ever see it and no computer systems outside yours know about it. So, it's merely an implementation detail and should not be a part of the model. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New feature: skip row locks when table is locked.
On Fri, 2011-04-29 at 10:25 +0200, Andres Freund wrote: On Thursday, April 28, 2011 11:44:37 PM Jeff Davis wrote: On Thu, 2011-04-28 at 07:29 +0200, pasman pasmański wrote: Hi. Yesterday i have an idea, that sometimes row locks may be skipped, when table is already locked with LOCK command. It may to reduce an overhead from row locks. What do you think about it? be using row locks if they already have an EXCLUSIVE lock on the table. Do you have a use-case in mind? It could possibly reduce the disk overhead of doing foreign key checks during large operations somewhat as fewer buffers would get dirtied. At least thats the situation where I thought about it before. Oh, that does sound like a good use case. Doesn't sound too hard to do either, unless I'm missing something. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for Silicon Valley/Peninsula/San Francisco users group
On Fri, 2011-04-29 at 14:58 -0700, Rick Genter wrote: I've been using PostgreSQL pretty steadily for the past year and am interesting in joining/attending a users group meeting. I've searched for a users group in the San Francisco/Peninsula/Silicon Valley area (I'm in Redwood City), but all I've found are references to a San Francisco group where the last update/meeting was 2 years ago. Is there such a group in this area? Thanks. Welcome! http://www.meetup.com/postgresql-1/ I'm not sure which reference you found, but SFPUG is certainly active with meetings every month. The next one is May 10. Check out the user group mailing list here: http://archives.postgresql.org/sfpug/ Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New feature: skip row locks when table is locked.
On Thu, 2011-04-28 at 07:29 +0200, pasman pasmański wrote: Hi. Yesterday i have an idea, that sometimes row locks may be skipped, when table is already locked with LOCK command. It may to reduce an overhead from row locks. What do you think about it? The table-level lock mode would need to be high enough to conflict with SELECT FOR UPDATE to prevent concurrent SELECT FOR UPDATEs from happening (or a SELECT FOR UPDATE and SELECT FOR SHARE happening concurrently). From: http://www.postgresql.org/docs/9.0/static/explicit-locking.html It looks like you'd need either EXCLUSIVE or ACCESS EXCLUSIVE lock mode as the table-level lock in order to skip the row-level lock. So, I think your optimization would work (at least I can't think of anything wrong with it), so long as the table-level lock is at least as strong as EXCLUSIVE. Seems fairly minor though -- most people would not be using row locks if they already have an EXCLUSIVE lock on the table. Do you have a use-case in mind? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] Streaming Replication limitations
On Wed, 2011-04-13 at 14:42 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: 2011/4/13 Tom Lane t...@sss.pgh.pa.us: Short answer is to test the case you have in mind and see. That's the long answer, not least because the absence of a failure in a test is not conclusive proof that it won't fail at some point in the future while in production. Not really. Every known source of incompatibility (endianness, alignment, float format, etc) is checked at postmaster startup via entries in pg_control. I seem to remember that Mac and Linux have a different notion of what en_US collation means (I couldn't find any standard anywhere to say that one was right and the other was wrong). So, that risks index corruption. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deferred foreign key constraint downsides
On Fri, 2011-04-08 at 14:08 -0500, Jack Christensen wrote: Are there any other downsides to just setting all my foreign keys to initially deferred? It may consume memory resources until the transaction is complete. Also, when it's possible to write the SQL in an order that always maintains the integrity of the FK, then it's usually more readable and understandable. So using immediate constraints may encourage a more readable style. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of memory
On Tue, 2011-04-05 at 21:50 +1200, Jeremy Palmer wrote: Hi, I've been having repeated troubles trying to get a PostgreSQL app to play nicely on Ubuntu. I recently posted a message on this list about an out of memory error and got a resolution by reducing the work_mem setting. However I'm now getting further out of memory issues during the same stage of plpgsql function as mentioned before. The function itself is run as part of larger transaction which does the following: Where is the source to the function? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique amount more than one table
On Tue, 2011-04-05 at 17:02 -0500, Perry Smith wrote: CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$ SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1; $$ LANGUAGE SQL; Next I added a check constraint with: ALTER TABLE table1 ADD CHECK ( unique_xxx() ); ... After I insert a row that I want to be rejected, I can do: select unique_xxx(); unique_xxx f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? Yes. But even if it ran afterward, there is still a potential race condition, because the query in the CHECK constraint doesn't see the results of concurrent transactions. To make this work, you should be using LOCK TABLE inside of a trigger (probably a BEFORE trigger that locks the table, then looks to see if the value exists in the view already, and if so, throws an exception). CHECK is not the right place for this kind of thing. Keep in mind that the performance will not be very good, however. There is not a good way to make this kind of constraint perform well, unfortunately. But that may not be a problem in your case -- try it and see if the performance is acceptable. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create unique index or constraint on part of a column
On Mon, 2011-03-07 at 23:30 +, Ruben Blanco wrote: Hi: Is there anyway to create a unique index or constraint on part of a column? Something like this, but something that works ;-) ALTER TABLE invoices ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date), innvoice_number); CREATE UNIQUE INDEX invoices_constraint_idx ON invoices (EXTRACT(YEAR FROM invoice_date), invoice_number); Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temporal period type and select distinct gives equality error
On Thu, 2011-01-27 at 07:32 -0500, Arturo Perez wrote: I thought I saw that in CVS but when I checked it out and installed it the error did not go away. Let me try that again. Do you think I'd need to reinstall the server itself to insure the proper behavior? No, reinstalling the extension should fix it. Also, there is a mailing list for this extension as well: http://lists.pgfoundry.org/pipermail/temporal-general/ Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temporal period type and select distinct gives equality error
On Wed, 2011-01-26 at 09:27 -0500, Arturo Perez wrote: and yet when I do a select distinct with a join I get QueryLogger - *** error. org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type period If I leave the period column out of the select list then it works fine. Did I install it wrong? Or is something else going on? tia arturo No suggestions from anyone? Anyone out there? :-) Sorry, I missed this the first time. I have added support for this in the CVS repository, but have not included it in the latest release. The thing that's missing from the released files is a btree opclass, which is what postgresql uses to find the right equality operator for DISTINCT. I am currently working on range types which is a patch that will provide support for PERIOD, as well as many other range types, in core. That will, of course, support everything you expect, including DISTINCT on the range type. We're currently in the middle of the final commit fest for 9.1. After this is over (or perhaps earlier, if I can find the time), I'll do some clean up of the existing pgsql-temporal PERIOD type, and do a release of that. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint exclusion with box and integer
On Fri, 2011-01-21 at 18:36 +, Daniele Varrazzo wrote: = alter table commission_rate add constraint check_overlapping exclude using gist (payer_id with =, box( point(extract(epoch from start_date), extract(epoch from start_date)), point(coalesce(extract(epoch from end_date), 'infinity'), coalesce(extract(epoch from end_date),'infinity') ) ) with ); ERROR: data type integer has no default operator class for access method gist HINT: You must specify an operator class for the index or define a default operator class for the data type. Can I build a constraint check using both a box (for the range) and an integer (for a fkey) or should I go back to the classic trigger + lock solution? Install btree_gist, and this should work. Exclusion constraints are enforced with an index, and an index can only have one index access method (btree, gist, etc.). So you need to have one index access method that works for both = on integers and on boxes. There's no hope of making a btree work for on boxes, so we need to make gist work for = on integers. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Are there any projects interested in object functionality? (+ rule bases)
On Fri, 2011-01-21 at 09:10 -0600, Andy Colson wrote: On 1/21/2011 2:16 AM, Nick Rudnick wrote: Dear all, for the sake academic teaching, a colleague asked me in how far PostgreSQL does support object functionality these days. Although postgres at one time had ORDBMS as a goal, I haven't seen any real interest or work in that area for a long time. The OO features are primarily: 1. OIDs -- hidden columns with auto-generated number. At one time, OIDs were a part of every table, now they must be specified explicitly. They still remain on many of the system catalogs, but not all. Mostly, they are now just an implementation detail that people only notice when looking at the catalogs (and I am personally annoyed that they are hidden, because when looking at the catalogs usually you want to see the OIDs). 2. Inheritance This feature is now used almost exclusively for physical partitioning rather than logical design. You do realize that ORM sucks, right? Andy, please try to keep the criticism constructive. Nick, I think the problem with ORDBMS is that they essentially introduce pointers in the data, and that adds a lot of complexity. For instance, the relational algebra has nice closure properties. If you join two relations, you get another relation. But what if you introduce pointers? Well, then each table might start out with OIDs, but then when you join with another table and do a GROUP BY, you lose any meaningful OIDs. So what do you have? Something else. Therefore, you've lost closure. Now, maybe there is a good reason to sacrifice closure and other nice properties of the relational model. But there is a perception among many people (like Andy) that the case has not been made. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help writing exclusion constraint
On Wed, 2011-01-19 at 10:15 -0500, Daniel Popowich wrote: Anyway...Jeff, all your answers depend on using new features in 9.0. What would you recommend for folk still using 8.4? Without 9.0 exclusion constraints, what else can you do besides using functions in check constraints (or triggers) with appropriate locking (at some level of the overall application architecture). There are several approaches, but all of them leave something to be desired, of course. I break the alternative solutions into 4 categories: 1. Full table lock -- instead of using a CHECK constraint, use a trigger and acquire a full table lock. The obvious problem here is the contention over that lock, so transactions will need to be kept short. Performance with this approach will not be very good, but perhaps that's OK in some situations. 2. What I call quantization. That is, choose a size, say one hour, and assume that 1:00 really means 1:00 - 2:00. Then you can use a UNIQUE index. You have to align everything on the hour exactly (you can't do 1:30-2:30, for instance), and longer reservations require multiple entries. Choosing an appropriate chunk size is difficult, because if it's too big then it makes the application overly strict (and imposes inconveniences on your organization); but if you choose a size that is too small, it requires many entries for a single reservation (if you choose one minute, then a one-hour reservation requires 60 rows). These drawbacks are acceptable for some organizations, but not all. 3. Complex procedural code can be used. For instance, you might have a separate table (call it my_locks) of rows that exist just for row-level locks. One row would represent 1:00 - 2:00, another 2:00 - 3:00, etc. And when you go to insert into the main table (call it reservations), you take a row-level lock on every row in my_locks that overlaps with the time you are inserting. So, if you are inserting 10:30 - 11:30 into the reservations table, you would take a lock on the rows 10:00 - 11:00 and 11:00 - 12:00 in your my_locks table. This effectively partitions your lock space, so that a reservation for 1:30 - 2:30 won't have to wait for a reservation between 10:30 and 11:30. There are other ideas along these lines as well, this is just an example of how adding complexity can help. Be careful though, the complexity explodes pretty quickly, and there are a lot of hidden performance traps. 4. You can work outside of the transactional system. Record both reservations, and check later for conflicts. The problem here is what to do when you find one. If you want to undo one reservation, and it was part of a larger transaction, you have to figure out how to undo the whole transaction. And you need to keep a log of which transactions need to be checked, so that if there is a crash you don't lose track and leave the conflicting reservations in there. As you can see, none of these are ideal. But, if you run into a specific problem, you can usually pick one of these approaches and make it work with careful determination. Exclusion constraints are much easier, however ;) Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help writing exclusion constraint
On Sat, 2011-01-15 at 19:17 +, Matthew Wilson wrote: create table event( destination_id integer not null references destination (destination_id), starts timestamp, ends timestamp ); I want to make sure that no two rows **with the same destination_id** overlap in time. First, you need to have some notion of overlaps, so you need to combine the starts and ends into a single value. I recommend trying the PERIOD datatype (as Andreas suggests). They don't have to be in the same column necessarily (you could use a functional index that combines the values), but typically it would be helpful anyway. If you use the PERIOD datatype, the overlaps operator is . So, assuming that the combined start/end is called during, the exclusion constraint might look something like: EXCLUDE USING gist (destination_id WITH =, during WITH ) You'll need to install the contrib module btree_gist first, so that = is indexable over integers using GiST. What's the above constraint says is: rows R1 and R2 conflict if R1.destination_id = R2.destination_id AND R1.during R2.during, and it will prevent R1 and R2 from both existing at the same time in your table. This method will be safe from race conditions. Hope this helps. Also, for more detailed examples that happen to be very similar to your problem, see: http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/ http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/ Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help writing exclusion constraint
On Sat, 2011-01-15 at 15:07 -0500, Daniel Popowich wrote: Constraint expressions can only be simple boolean expressions, so can refer only to the column(s) of the current row you're inserting/updating, Exclusion Constraints are a new feature in 9.0: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE They allow you to constrain across rows, much like UNIQUE (in fact, the constraints that can be expressed by an exclusion constraint are a superset of the constraints that can be expressed by UNIQUE). so to refer to other records (which you'll need to do to compare destination_ids) you need to create a function...something along the lines of this: ... ALTER TABLE event ADD CONSTRAINT event_overlap CHECK(overlap_at_dest(destination_id, starts, ends)); As Tomas said, that's an unsafe thing to do. I do not recommend using a table-reading function in a check constraint. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help writing exclusion constraint
On Sat, 2011-01-15 at 21:32 +0100, Tomas Vondra wrote: ALTER TABLE event ADD CONSTRAINT event_overlap CHECK(overlap_at_dest(destination_id, starts, ends)); There's a race condition ... One way to fix this is locking I do not recommend locking. In fact, the primary reason that exclusion constraints exist is to prevent unnecessary locking for problems exactly like this. I included some links in my other reply that demonstrate how to avoid that excessive locking while still being safe from race conditions. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Shit happens
On Sun, 2011-01-02 at 10:31 +0100, Dick Kniep wrote: Hi list, Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not directly possible to get that data, but I am quite desparate to get the data back. If one way or another the data is (except for the 4 days we really have no data for) accessible, we will write a program to recover the data into the production database. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference where to find enough information to do this) I would be very happy. The WAL segment files will contain a lot of information (including some full page images), which may be enough to recover some of your data. Obviously, you'll be missing most of the data from that 4-day period, but there is some hope. However, pulling that data out of the WAL and making sense out of it will be tricky, indeed (as Radosław already mentioned). Email lists are probably not the best way to make it through this kind of tricky recovery. You might consider contacting one of the PostgreSQL consulting companies: http://www.postgresql.org/support/professional_support where someone experienced with WAL recovery can help you personally. Some of these organizations employ people who have done extensive development on the WAL recovery system and know it _very_ well. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] B-tree + sorting + unique constraint
On Wed, 2010-12-29 at 16:39 -0800, bricklen wrote: -- works: create unique index t_uidx on t (x desc nulls last,y desc nulls last, z asc); drop index t_uidx; ... -- creating the unique constraint with sorting fails: alter table t add constraint t_xyz_uc unique (x desc nulls last,y desc nulls last, z asc); ERROR: syntax error at or near desc LINE 1: alter table t add constraint t_uidx unique (x desc nulls las... A UNIQUE constraint in a table definition only allows simple column references. To get more advanced, you need to use the CREATE UNIQUE INDEX syntax you showed above. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] v9 deployment advise
On Mon, 2010-11-15 at 13:25 -0700, Gauthier, Dave wrote: Are there any serious known bugs left in 9.0.1 that will be addressed in 9.0.2 (if there even will be a 9.0.2)? Take a look here: http://git.postgresql.org/gitweb?p=postgresql.git;a=log;h=refs/heads/REL9_0_STABLE at everything that happened after 9.0.1. It's up to you whether you consider those serious or not, but I don't think there are any major issues that should prevent deployment. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using EXCLUDE in 9.0 with operator ...
On Wed, 2010-11-10 at 13:45 +1300, Derek Harland wrote: Theoretically the following would be nice if it worked EXCLUDE (X WITH =, Y WITH ) but it complains that ERROR: operator (text,text) is not a member of operator family text_ops because the Btree index method only allows = in an exclude constraint. Or am I missing a simpler way of doing this? (without having to create and then foreign key to another table, which is obviously a possibility) Try using 9.1alpha ( http://www.postgresql.org/developer/alpha ) and installing btree_gist. Then, use: EXCLUDE USING gist (X WITH =, Y WITH ) In 9.0 this particular constraint won't work because there is an additional sanity check in the code that won't pass if the operator is . The sanity check was deemed worthwhile for the first release of the feature, but will be lifted in version 9.1. Also, doesn't work (yet) with btree, but in principle there is no reason why not. Perhaps for 9.1 as well. Can you please share your use case for this particular constraint? I'd like to hear it. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
