Re: [GENERAL] autovacuum worker running amok - and me too ;)
wambacher writes: > My system has 24GB of real memory but after some hours one autovacuum worker > is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the > process with kill -9 and the postgresql-server is restarting because of that > problem. > i changed the base configuration to use very small buffers, restartetd the > server twice but the problem still exists. > i think, it's allways the same table and that table is huge: 111GB data and > 3 Indices with 4GB, 128 GB and 12 GB. It's the table planet_osm_ways from > openstreetmap. maybe that helps. Maybe you could reduce the statistics targets for that table. I think we've heard that the analyze functions for PostGIS data types are memory hogs, too --- maybe it's worth inquiring about that on the postgis mailing lists. regards, tom lane -- 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] autovacuum worker running amok - and me too ;)
wambacher writes:
> Tom Lane-2 wrote
>> Maybe you could reduce the statistics targets for that table.
> don't understand what you mean. do you mean how often that table is
> autovacuumed? at the moment about once a day or once in two days, i think.
No, I mean the amount of stats detail that ANALYZE tries to collect.
I'm guessing that it's not auto-vacuum but auto-analyze that's getting
OOMed.
See ALTER TABLE SET STATISTICS TARGET.
regards, tom lane
--
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 failing from 9.3 to 9.4 because "template0" already exists
Stephen Frost writes: > * Matt Landry (lelnet.m...@gmail.com) wrote: >> postgres=# select datname, datallowconn from pg_database ; >> datname | datallowconn >> ---+-- >> template1 | t >> template0 | t >> postgres | t >> reporting | t >> (4 rows) > Right, as I mentioned, template0 shouldn't have datallowconn as 'true'. > That's why it's being included in the pg_dumpall. Perhaps pg_upgrade should deliberately ignore template0 regardless of datallowconn? And/or we should hard-wire that into pg_dumpall? I feel no compulsion whatsoever to preserve any user-initiated changes in template0 across an upgrade. regards, tom lane -- 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 failing from 9.3 to 9.4 because "template0" already exists
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Perhaps pg_upgrade should deliberately ignore template0 regardless of >> datallowconn? And/or we should hard-wire that into pg_dumpall? > My thinking would be that pg_dumpall should be hard-wired for template0 > (just like it is for template1..) and that we should *not* be excluding > databases that are marked as datallowconn = false.. That said, it's not > clear to me what to do there instead. Maybe throw an error or a > warning? The point of pg_dumpall is to dump *all* the databases and at > least the manpage doesn't appear to say anything about "but ignores > databases with datallowconn = false". I think pg_upgrade and pg_dumpall may be two different use-cases. pg_upgrade should definitely throw a hard error if there are any non-template0 databases that it can't connect to, because the alternative is losing such databases during the upgrade. I'm not sure that the argument is so black-and-white for pg_dumpall, though. Nobody's ever complained about it skipping unconnectable databases, and that behavior has been there since we invented datallowconn (cf commit 2cf48ca04bf599). regards, tom lane -- 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] VACUUM FULL doesn't reduce table size
David G Johnston writes: > I have no experience here but given recent versions rewrite the table the > vacuum verbose output shown seems unusual. "vacuum verbose output shown?" There wasn't any. [ digs about ... ] Oh. The version of the message that nabble sent to the postgresql lists was missing vital parts of what got posted at nabble: compare http://www.postgresql.org/message-id/1425639585904-5840782.p...@n5.nabble.com http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-td5840782.html I've seen that before. I'm about ready to propose that we flat out ban messages from nabble to the PG lists; I'm tired of them wasting our time with this sort of BS. (FWIW, the output shown on nabble doesn't look materially different from what I see in HEAD. It also proves positively that the OP has uncommitted transactions blocking cleanup of known-dead rows. But we were just guessing at that over here.) regards, tom lane -- 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-related legal question
Michael Nolan writes:
> One of my sons was hired by Google last year after spending the past
> several years working on various open-source projects, it took 2 days of
> back-and-forth with Google's legal department before he was satisfied with
> the restrictions in their offer.
FWIW, I had a pretty similar discussion with Salesforce when I joined
them.
If you're looking at an employment agreement with verbiage like this,
get them to modify it. They're probably hiring you in part *because*
you are a contributor to PG, so they should be willing to bend their
standard language for you. If not, maybe you don't want that job.
regards, tom lane
--
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] regclass and format('%I')
Jason Dusek writes:
> It honestly seems far more reasonable to me that %s and %I should do
> the exact same thing with regclass.
You're mistaken. The operation of format() is first to convert the
non-format arguments to text strings, using the output functions for their
data types, and then to further process those text strings according to
the format specifiers:
%s -- no additional processing, just insert the string as-is.
%I -- apply double-quoting transformation to create a valid SQL identifier.
%L -- apply single-quoting transformation to create a valid SQL literal.
In the case of regclass, the output string is already double-quoted
as necessary, so applying %I to it produces a doubly double-quoted
string which is almost certainly not what you want. But it's not
format()'s job to be smarter than the user. If it tried to avoid
an extra pass of double quoting, it would get some cases wrong,
potentially creating security holes.
regards, tom lane
--
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 there a relationship between indexes and temporary file creation?
Seref Arikan writes: > I have a table with 10s of millions of rows and I'm running a fairly > complex query with a lot of self joins. > This is an experimental db and the table structure needs to stay as it is. > I've noticed that some queries are not completed because postgres uses all > available space on disk for temporary data when doing complex joins. > The table has no indexes at the moment; I'm measuring performance with and > without indexes so I don't mind long query times. Running out of disk space > is a problem though.I can't think of a connection between indexes and the > temporary space needed for joins but I hope I can get some input from those > with (a lot) more knowledge about potgres internals. > Is there any possibility of indexing decreasing the required temporary disk > space when performing complex joins? Hm. Your alternatives don't seem terribly good here. Presumably you're running out of space because either a merge or hash join will spill temp data to disk if there's too much data. While an indexed nestloop join wouldn't have that problem, for the amount of data you're apparently dealing with, you might be dead of old age before it finishes :-(. A slightly more promising idea is that an indexscan might substitute for the sort needed by a mergejoin, so again not needing temp space; but this still likely implies a whole lot of random I/O which will take forever, unless maybe your data is more or less in order by the join key. In short I doubt that indexes will be magic bullets for enormous joins. You probably need to be smarter about what the queries are asking for. But without a lot more detail than you've provided here, we're not going to be able to help much about that. If you'd like additional advice, I'd recommend reviewing https://wiki.postgresql.org/wiki/Slow_Query_Questions and then taking the discussion to the pgsql-performance list. regards, tom lane -- 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 there a relationship between indexes and temporary file creation?
Seref Arikan writes:
> Another idea that popped into my head is to consider table partitioning. Am
> I correct to assume that (inevitable) joins could benefit from partitions
> when sub query criteria constraints results to a particular partition?
That's too vague to answer really. My suspicion is that you'd still end
up having to modify the queries, ie hand-factoring the joins, but maybe
you wouldn't need to. You could experiment easily enough; just set up a
toy database with not much data in it and see if you get plans that don't
scan all the partitions.
regards, tom lane
--
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] regclass and format('%I')
"David G. Johnston" writes:
> âIOW, as long as the output string matches: ^"(?:"{2})*"$ I do not see how
> it is possible âfor format to lay in a value at %I that is any more
> insecure than the current behavior. If the input string already matches
> that pattern then it could be output as-is without any additional risk and
> with the positive benefit of making this case work as expected. The broken
> case then exists when someone actually intends to name their identifier
> <"something"> which then correctly becomes <"""something"""> on output.
But that's exactly the problem: you just broke a case that used to work.
format('%I') is not supposed to guess at what the user intends; it is
supposed to produce a string that, after being passed through identifier
parsing (dequoting or downcasing), will match the input. It is not
format's business to break that contract just because the input has
already got some double quotes in it.
An example of where this might be important is if you're trying to
construct a query with arbitrary column headers in the output. You
can do
format('... AS %I ...', ..., column_label, ...)
and be confident that the label will be exactly what you've got in
column_label. This proposed change would break that for labels that
happen to already have double-quotes --- but who are we to say that
that can't have been what you wanted?
regards, tom lane
--
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] Preserving formatting and comments in a VIEW definition
Robert James writes:
> When I save a VIEW, Postgres seems to convert it to a different
> format, functionally equivalent but unrecognizable (whitespace,
> comments, adds lots of casts, etc.)
> Is there any simple way to preserve my original code?
The usual suggestion is to keep your SQL code in an SCM repo, somewhere
outside the database. Postgres itself saves views as parse trees, so
there's no way it will ever remember whitespace or comments.
regards, tom lane
--
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] Slow query with join
Tomas Vondra writes: > On 16.3.2015 19:50, Marc Watson wrote: >> I hope someone can help me with a problem I'm having when joining a >> view with a table. The view is somewhat involved, but I can provide the >> details if necessary > First, get rid of the ORDER BY clauses in the subselects - it's > completely pointless, and might prevent proper optimization (e.g. > replacing the IN() with optimized joins. I'm suspicious that the cause may be an ORDER BY in the view. It's hard to tell when we've not seen the view definition, but I see that both plans we've been shown are going to produce output sorted by actor.id. Maybe that's happenstance, or maybe not. regards, tom lane -- 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] Unexpected array_remove results
Matija Lesar writes:
> should not in example below array_remove return same results?
AFAICS, array_remove keeps the existing lower bound number. I don't
see anything particularly wrong with that definition.
Even if we didn't care about backwards compatibility, it would require
nontrivial effort to change it --- for example, there are several
early-exit cases that return the original array unmodified, and that would
be wrong if we were to adopt some other definition such as "force the
lower bound to 1".
regards, tom lane
--
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] xml
Pavel Stehule writes:
> result of xmlagg is not valid xml.
Really? Either that's a bug, or it's declared wrong.
regards, tom lane
--
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] scope quirk in copy in function
Adrian Klaver writes: > On 03/23/2015 06:59 AM, Scott Ribe wrote: >> create or replace function archive_some_stuff() returns void as $$ >> declare cutoff timestamptz; >> begin >> cutoff := now() - '1 day'::interval; >> copy (select * from log where end_when < cutoff) to ... > "Variable substitution currently works only in SELECT, INSERT, UPDATE, > and DELETE commands, because the main SQL engine allows query parameters > only in these commands. To use a non-constant name or value in other > statement types (generically called utility statements), you must > construct the utility statement as a string and EXECUTE it." Yeah. It seems like SELECT-inside-COPY might be worth special casing though. ISTM this is more or less analogous to the case of PREPARE or DECLARE CURSOR, which are also utility commands that contain a regular DML command. I'm pretty sure there is a hack in there that allows parameters to be transmitted down through PREPARE or D.C. ... regards, tom lane -- 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] inputs into query planner costing
Mike Roest writes: >I'm having an issue with query performance between 2 different pgsql > environments. > Ther first is our current production postgres server with is running 9.3.5 > on Centos 5 x64. The second system is Amazon's RDS postgres as a service. > On our local DB server we have a query that executes in a reasonable amount > of time (600 msec). On RDS the query will run for more then 10 minutes on > a similarly CPU specced systems. I've been working through with Amazon > support and I'm looking for more suggestions on where to look (both for me > and to direct Amazon). The RDS system does use a network filesystem while > our production server is a local RAID10 array, I can see that effecting the > actual performance of the query but not the query planner costing (unless > there's an input to query planner costing that I can't find) > The Query plan costs generated by the 2 systems are vastly different, while > the plans themselves are basically identical other then the materialization > that RDS is doing (if I disable the materialization then they are almost > the same other then a seq scan/heap scan on one small <2000 row table). > All the tables in the query have been analyzed on each server without any > impact Last I checked, there was not any magic pixie dust in the planner ;-). Your results have to be explained by one or more of these things: 1. Not same version of Postgres between the two systems. 2. Not same planner parameter settings. 3. Different physical table sizes. 4. Different ANALYZE statistics. As for #1, I have no idea whether Amazon RDS runs a purely stock Postgres release or has some custom modifications of their own, but it'd be worth asking about that. As for #2, you say you checked that, but I'm dubious. In particular this discrepancy: Index Scan using uix2pdas_userpolicy on policydataaccessscope policydataaccessscope31 (cost=0.28..8.30 rows=1 width=16) Index Scan using uix2pdas_userpolicy on policydataaccessscope policydataaccessscope31 (cost=0.28..4.30 rows=1 width=16) is hard to explain unless the second system is using a smaller random_page_cost than the first. Maybe somebody used ALTER ROLE SET or ALTER DATABASE SET to adjust parameters in a way that only affects some roles/databases? I suspect that the large differences in some of the seqscan costs might be explainable by #3, ie those tables are bloated with lots of empty space on one system but not the other. Comparing pg_relation_size() would be the way to find out. I mention #4 for completeness. ANALYZE uses random sampling, so it's expectable that the data distribution stats would be a bit different on the two systems, but large differences that persist across multiple ANALYZE attempts are unlikely. (Although ... you do have the same default_statistics_target on both systems, no? Table-specific statistics targets could be a gotcha as well.) regards, tom lane -- 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 does CREATE INDEX CONCURRENTLY need two scans?
Michael Paquier writes: > On Wed, Apr 1, 2015 at 9:43 AM, Joshua Ma wrote: >> Why are two scans necessary? What would break if it did something like the >> following? >> >> 1) insert pg_index entry, wait for relevant txns to finish, mark index >> open for inserts >> >> 2) build index in a single snapshot, mark index valid for searches >> Wouldn't new inserts update the index correctly? Between the snapshot and >> index-updating txns afterwards, wouldn't all updates be covered? > When an index is built with index_build, are included in the index only the > tuples seen at the start of the first scan. A second scan is needed to add > in the index entries for the tuples that have been inserted into the table > during the build phase. More to the point: Joshua's design supposes that retail insertions into an index can happen in parallel with index build. Or in other words, that index build consists of instantaneously creating an empty-but-valid index file and then doing a lot of ordinary inserts into it. That's a possible design, but it's not very efficient, and most of our index AMs don't do it that way. btree, for instance, starts by sorting all the entries and creating the leaf-level pages. Then it builds the upper tree levels. It doesn't have a complete tree that could support retail insertions until the very end. Moreover, most of the work is done in storage that's local to the backend running CREATE INDEX, and isn't accessible to other processes at all. regards, tom lane -- 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] Would like to know how analyze works technically
TonyS writes: > Running "analyze verbose;" and watching top, the system starts out using no > swap data and about 4GB of cached memory and about 1GB of used memory. As it > runs, the amount of used RAM climbs, and eventually the used swap memory > increases to 100% and after being at that level for a couple of minutes, the > analyze function crashes and indicates "server closed the connection > unexpectedly." ANALYZE is supposed to work in a constrained amount of memory, though that amount depends quite a bit on what the data is and what you've got the statistics target set to. We've heard reports that there are memory leaks in (some versions of?) PostGIS's analyze support. Whether that would apply to you would depend on whether you're using PostGIS. Hard to say more without a lot more concrete info about what your data is, what PG version you're using, etc. regards, tom lane -- 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] Creating a non-strict custom aggregate that initializes to the first value
"David G. Johnston" writes: > On Thu, Mar 26, 2015 at 1:49 PM, Timothy Garnett > wrote: >> but if that is declared strict then it would take the first non-null value >> and return A in my second example, if declared non-strict then the initial >> state would be fed as null rather then the first value. Is there a way to >> declare the function non-strict (so that null values are passed) but still >> have it initialize to the first value like it would if it was strict? > You want NULL to both mean "not initialized" and "unknown value" which is > impossible and SQL does not provide any other universal literal that means > one or the other. Yeah. You need distinct representations for "nothing seen yet" and "saw a NULL"; the built-in behavior doesn't suffice for this. One idea is for the state value to be of anyarray type: initially null, and a one-element array containing the first input value once you've seen that. It strikes me though that this aggregate is ill-defined by nature. In particular, if you're going to treat NULL as being a real data value, then what're you gonna return when there were no input rows? You won't be able to distinguish "no input rows" from "first input row had a NULL". Maybe you should rethink whatever activity you were wanting it for. regards, tom lane -- 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] now() vs 'epoch'::timestamp
James Cloos writes: > I've for some time used: >(now()::timestamp without time zone - 'epoch'::timestamp without time > zone)::reltime::integer > to get the current seconds since the epoch. The results are consistant > with date +%s. > (Incidently, is there a better way in 9.4?) > But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off. > I consitantly get 1970-01-01 06:00 plus a fraction of a second from: > select now() - ((now()::timestamp without time zone - 'epoch'::timestamp > without time zone)::reltime::integer)::reltime; "reltime" doesn't have fractional-second precision, so you lose whatever part of the original timestamp difference was fractional. "reltime" is deprecated too, and will go away someday (probably long before this calculation starts to overflow an int, in 2038), so you really don't want to be using it. regards, tom lane -- 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] Would like to know how analyze works technically
TonyS writes:
> The analyze function has crashed again while the overcommit entries were
> as above. The last bit of the PostgreSQL log shows:
> MdSmgr: 41934848 total in 14 blocks; 639936 free (0 chunks); 41294912 used
> ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> hba parser context: 7168 total in 3 blocks; 2288 free (1 chunks); 4880 used
> LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
> ErrorContext: 8192 total in 1 blocks; 8160 free (6 chunks); 32 used
> 2015-04-01 14:23:27 EDT ERROR: out of memory
> 2015-04-01 14:23:27 EDT DETAIL: Failed on request of size 80.
> 2015-04-01 14:23:27 EDT STATEMENT: analyze verbose;
We need to see all of that memory map, not just the last six lines of it.
regards, tom lane
--
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] quick q re execute & scope of new
Scott Ribe writes:
> Easier to give an example than describe the question, any chance of making
> something like this work?
> execute('insert into ' || tblname || ' values(new.*)');
Not like that, for certain. It might work to use EXECUTE ... USING new.*
or some variant of that.
regards, tom lane
--
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] Error handling in C API function calls in a way that doesn't close client connection
Igor Stassiy writes:
> This question refers to version 9.4 of Postgres. I have have a function
> Datum do_something(PG_FUNCTION_ARGS)
> {
> ...
> if(error_occured) {
> ereport(ERROR, (errmsg("some error occured")));
> }
> ...
> }
> When I call do_something in a way to deliberately cause the error
> condition, the client connection (psql) is terminated,
[ shrug... ] You did not show us whatever is causing that problem.
Given a self-contained example, it would be possible to offer some
useful advice. On the basis of what you've said here, though, I can
only suggest that you're looking for the problem in the wrong place.
regards, tom lane
--
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] "could not split GIN page; no new items fit"
Chris Curvey writes: > Hmm, I'm trying to create a gin index, thusly: > create index foo_idx on foo using gin(entry gin_trgm_ops); > and I'm getting the error "could not split GIN page; no new items fit" > Any idea what this means, or how I can get around it? Looks to me like a bug (ie, the code seems to think this is a can't-happen case). Don't suppose you could supply sample data that triggers this? regards, tom lane -- 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] Problems with casting
Jim Nasby writes: > I've created a variant data type [1]. It seems to work pretty well, > except for some issues with casting. > Since the idea of the type is to allow storing any other data type, it > creates casts to and from all other types. At first these were all > marked as ASSIGNMENT, but that made using variant with functions quite > cumbersome. With functions that accepted a variant, you still had to > explicitly cast it: > SELECT variant_function( some_field::variant.variant ) FROM some_table; > I was reluctant to make the casts to variant IMPLICIT, but it seems like > it actually works rather well... except for arrays: I suspect that that's only the tip of the iceberg. Remember the mess we had with implicit casts to text? And those only existed for a dozen or so types, not for everything. Every function or operator you define for "variant" is going to be a loaded gun just waiting to shoot your foot off, if you make all those casts implicit. regards, tom lane -- 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] Problems with casting
Jim Nasby writes: > On 4/7/15 4:17 PM, Tom Lane wrote: >> I suspect that that's only the tip of the iceberg. Remember the mess >> we had with implicit casts to text? And those only existed for a dozen >> or so types, not for everything. Every function or operator you define >> for "variant" is going to be a loaded gun just waiting to shoot your foot >> off, if you make all those casts implicit. > Yeah, that's why I avoided it. But that makes using it in a function a > real pain. :( I think this is a bit of a different scenario though, > because I don't see why you'd want to overload a function to accept both > variant and some other type. > Really what I want is for casting to variant to be a last-choice option, > and even then only for function calls, not operators. I believe that > would be safe, because then you'd have to explicitly be calling a > function, or explicitly doing something::variant = variant. Just out of curiosity, what's the point of this type at all, compared to "anyelement" and friends? regards, tom lane -- 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] Problems with casting
Jim Nasby writes: > On 4/7/15 4:35 PM, Tom Lane wrote: >> Just out of curiosity, what's the point of this type at all, compared >> to "anyelement" and friends? > The two big differences are that you can store a variant in a table > (with reasonable protection against things like dropping the underlying > type out from under it), and you can readily determine what the original > type was. Well, and you're not limited to a single type in a function as > you are with polymorphic. I'm fairly skeptical of the idea that you should want to store a variant in a table --- smells of EAV schema design to me. What would a unique index mean on such a column, for instance? As for the other two, the only reason you can't do them with polymorphic arguments is nobody has wanted them bad enough to do something about it. regards, tom lane -- 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] "could not split GIN page; no new items fit"
Jim Nasby writes: > On 4/4/15 8:38 AM, Chris Curvey wrote: >> I can! I just copied the data to a new table, obfuscated the sensitive >> parts, and was able to reproduce the error. I can supply the script to >> create and populate the table, but that's still clocking in at 250Mb >> after being zipped. What's the best way of getting this data out to >> someone who can take a look at this? (Feel free to contact me off-list >> to coordinate.) > It would be nice if you could further reduce it, but if not I'd suggest > posting it to something like DropBox and posting the public link here. So far I've been unable to reproduce the failure from Chris' data :-( Don't know why not. regards, tom lane -- 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] ecpg rejects input parameters
Andrew Pennebaker writes: > I can't find a relevant section to address my specific problem: ecpg > complaining when I try to check the syntax of my .sql files that use input > parameters. I'm not sure why you think that should work. psql and ecpg have quite distinct input languages. Both are extensions of SQL, but the key word there is "extension". ecpg certainly isn't going to accept psql's backslash commands for instance, any more than psql would accept ecpg's C code portions. And I doubt it would be useful for ecpg to simply ignore the variable-interpolation symbols; but it has no way to know what's going to be substituted for those symbols. It would be more interesting to consider giving psql a syntax-check-only mode; though I'm afraid use of variable interpolation would still be pretty problematic, since the variables are commonly filled from execution of previous commands. regards, tom lane -- 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] Error Creating DBlink Extension in 9.4.1
"Nykolyn, Andy (AS)" writes: > I get the following error in my 9.4.1 database when I issue the command: > Create extension dblink; > ERROR: could not load library "/usr/local/pgsql941/lib/dblink.so": > /usr/local/pgsql941/lib/dblink.so: undefined symbol: PQsetSingleRowMode > Is this a bug or did I do something wrong? Any help would be greatly > appreciated. Thanks It looks like dblink.so is getting linked to an old version of libpq.so (very old, like pre-9.2). Probably that's whatever is in /usr/lib. If you don't want to update /usr/lib, you'll need to rebuild with an "rpath" pointing at the installation location for 9.4. (Note that PG usually is built with rpath pointing at whatever configure was told the installation location would be; so you might have brought this on yourself by relocating the directory tree from its initial location.) regards, tom lane -- 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] Invalid memory alloc
Jim Nasby writes:
> We need more information from the OP about what they're doing.
Yeah. Those NOTICEs about "nnn edges processed" are not coming out of
anything in core Postgres; I'll bet whatever is producing those is at
fault (by trying to palloc indefinitely-large amounts of memory as a
single chunk).
regards, tom lane
--
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] A question about plans and multi-key pks
Steve Rogerson writes:
> Why does the plan come up with 67 rows, when by definition there can be at
> most one as we are searching on the pk?
The rowcount estimate is made by combining selectivities of the WHERE
conditions; what you're seeing is a consequence of not having very good
knowledge about cross-column correlations. The actual details of the plan
(ie, that the pkey index can be used) are determined much later.
regards, tom lane
--
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] delete is getting hung when there is a huge data in table
Mitu Verma writes: > 1. If postgreSQL has some limitations for deletion of large data? Not as such, but you've not given us any details that would permit comment. A reasonably likely bet is that this table is referenced by a foreign key in some other table, and that other table has no index on the referencing column. That would make the FK is-it-ok-to-delete checks very slow. regards, tom lane -- 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] Hash function constant in the Hash join algorithm
Ravi Kiran writes:
> From the above two cases, I am understanding that even even when the hash
> function is made to return a constant, The hashjoin agorithm is taking
> significantly lower time compared to nested loop.
> Could anyone explain why does the hashjoin algorithm takes significantly
> lower time compared to nested loop when the hash function is made to return
> a constant value or have I done any mistake at any part of the code?
Well, hashjoin would suck the inner relation into memory (into its
hashtable) whereas nestloop would rescan the inner relation each time,
implying more buffer-access overhead, tuple visibility checking, etc.
A closer comparison might be to a nestloop that has a Materialize node
above its inner relation.
regards, tom lane
--
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 there no object create date is the catalogs?
Melvin Davidson writes:
> You are over thinking this. An object is only "created" once!
Yeah? Would you expect that pg_dump followed by pg_restore would preserve
the original creation date? What about pg_upgrade?
This has come up many times before, and we've always decided that it was
not as simple as it seems at first glance, and that it would be difficult
to satisfy all use-cases. Try searching the archives for previous threads.
regards, tom lane
--
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] Thousands of schemas and ANALYZE goes out of memory
Bruce Momjian writes:
> Is there a reason the following patch wasn't applied?
I don't think anybody ever did the legwork to verify it was a good idea.
In particular, it'd be good to check if sending a tabstat message for each
table adds noticeable overhead.
regards, tom lane
--
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] Thousands of schemas and ANALYZE goes out of memory
Bruce Momjian writes: > On Wed, May 13, 2015 at 06:10:26PM -0400, Tom Lane wrote: >> Bruce Momjian writes: >>> Is there a reason the following patch wasn't applied? >> I don't think anybody ever did the legwork to verify it was a good idea. >> In particular, it'd be good to check if sending a tabstat message for each >> table adds noticeable overhead. > OK, I will mark it as closed then. Thanks. I don't know that it should be closed exactly --- if we don't do this, we should do something else about the performance issue. Maybe put it on TODO? regards, tom lane -- 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 INTO and ON COMMIT
Bruce Momjian writes:
> On Wed, May 13, 2015 at 06:05:43PM -0600, Yves Dorfsman wrote:
>> Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql
>> functions, to select into a record.
> The pl/psql `SELECT INTO` is not related to the SQL SELECT INTO command
> --- yeah, confusing, so no, the pl/psql ability is not deprecated.
Part of the argument for deprecating the SQL-level SELECT INTO is
exactly that it's confusingly spelled the same as plpgsql's SELECT INTO,
but it means something totally different.
regards, tom lane
--
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] Slaves show different results for query
Adrian Klaver writes:
> The reason I ask is that the machine you are having problems with has OS
> X. Over the years I have seen quite a few reports on this list of OS X
> locale/encoding issues.
Yes. Here's the problem: OS X UTF8 locales (other than C) don't sort the
same as UTF8 locales on Linux. Because of this, the index created by the
master is effectively corrupt from the standpoint of the OS X slave; it's
not in the correct sort order. It might sometimes find the right results
anyway, but often not.
You might be able to get away with the described configuration if you
recreate the database using C locale, but personally I wouldn't risk it.
Masters and slaves in a WAL-shipping replication configuration should be
on substantially identical platforms, else you're just asking for trouble.
regards, tom lane
--
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] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?
Bill Moran writes: > My other question: is there a specific reason why PostgreSQL doesn't support > this syntax, aside from "nobody has bothered to add such support"? Because > I'm considering writing a patch to Postgres and submitting it, but I'm not > going to go down that path if there's a specific reason why supporting this > syntax would be _bad_. Personally, I feel like it would be a good thing, as > it seems like a lot of other database systems support it, and even though > it's not ANSI, it's pretty much the de-facto standard. How many is "a lot", and do any of the responsible vendors sit on the SQL standards committee? One large concern about doing anything like this is whether future versions of the SQL standard might blindside us with some not-terribly-compatible interpretation of that syntax. If we do something that is also in Oracle or DB2 or one of the other big boys, then we can probably rely on the assumption that they'll block anything really incompatible from becoming standardized ;-). OTOH, if the actual meaning of "a lot" is "MySQL", I'd be pretty worried about this scenario. regards, tom lane -- 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] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?
Alban Hertroys writes: > On 22 May 2015 at 04:46, Bill Moran wrote: >> With all that being said, if I were to build a patch, would it be likely >> to be accepted into core? > Wouldn't you also need to support similar syntax for octal numbers for > the patch to be complete? Or are those already supported (ISTR that's > '077' for decimal 63)? A patch that made 077 be interpreted as octal would certainly get rejected out of hand, because that's valid syntax right now and it doesn't mean 63. A similar objection might be raised to 0x..., as that is also valid syntax; it's read as 0 followed by an identifier: regression=# select 0xff; xff - 0 (1 row) (Yet another place where the fact that AS is optional yields surprising results...) So there would be a backwards compatibility break here, and no you can't fix it with a GUC. Still, it might be a small enough break that we could get away with it. I'm not personally very excited but other people might be. Other questions you'd have to think about: what is the data type of 0x; what do you do with 0x (too big even for int8). And it'd likely behoove you to check how Microsoft answers those questions, if you want to point to SQL Server as what's going to keep you out of standards-compatibility problems. (IOW, if 0x ever did get standardized, the text might well match what SQL Server does.) regards, tom lane -- 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] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?
Bill Moran writes: > Tom Lane wrote: >> Other questions you'd have to think about: what is the data type of >> 0x; what do you do with 0x (too big >> even for int8). And it'd likely behoove you to check how Microsoft >> answers those questions, if you want to point to SQL Server as what's >> going to keep you out of standards-compatibility problems. (IOW, >> if 0x ever did get standardized, the text might well match what >> SQL Server does.) > MSSQL seems to use it specifically for the equivalent of BYTEA types, > and it seems to me that should be how it works in PostgreSQL. Oh really? Wow, I'd just assumed you wanted this as a way to write integers. That's certainly the use-case I would have personally. I'm not even sure I like the idea of being able to write byteas without quotes --- they seem like strings to me, not numbers. > If an implicit cast from a 4-byte BYTEA to int works now, then it > should work ... otherwise an explicit cast would be needed, with the > same behavior if you tried to specify a number that overflows an int > in any other way. There's no cast at all from bytea to int. For one thing, it's quite unclear what endianness should be assumed for such a cast. (To get unsurprising behavior from what you're describing, I think we'd have to use a big-endian interpretation of the bytea; but that would be a pain for a lot of other scenarios, or even for this case if you'd written a bytea of length other than 4 or 8 bytes.) regards, tom lane -- 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] Server tries to read a different config file than it is supposed to
twoflower writes: > So I wrote a few scripts to make my life easier, e.g. *pg94start.sh*: > su postgres -c "/usr/lib/postgresql/9.4/bin/pg_ctl -D > /storage/postgresql/9.4/data -o '-c > config_file=/etc/postgresql/9.4/main/postgresql.conf'" > But running this script did not work, the server would not start. Testing this, the problem appears to be that you forgot the keyword "start", so pg_ctl didn't really do anything. It's always a good idea to redirect pg_ctl's stdout/stderr somewhere, so that you can look at it in event of problems. (It will *not* magically go to the server log file.) > So I > checked the log file and there was: > *FATAL: could not open file "/storage/postgresql/9.4/data/postgresql.conf": > Permission denied* I suspect this was left over from some previous attempt. > After fixing the ownership of this file, it worked. I can't explain that claim, but for me, -c config_file=something seems to work as you'd expect, and a look at the server source code confirms that it should honor that (cf SelectConfigFiles()). I think the documentation Adrian pointed to is a bit out of date, or at least oversimplified. One possible theory is that you had an "include" directive in the config file in /etc, causing it to try to read the other one? regards, tom lane -- 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] Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?
Thomas Kellerer writes:
> The SQL standard already specifies the format for "binary strings":
> ::=
> X [ ... ] [ { [ ... ] [ ...
> ] }... ]
> [ { [ ... ] [ { [ ... ]
> [ ... ] }... ] }... ]
Yeah, and we do honor that:
regression=# select x'1234abcdf';
?column?
--
0001001000110100101010001101
(1 row)
... although I notice we don't allow spaces in the input, which seems
like something to fix. (On further investigation, it looks like the
optional spaces are new since SQL99, which probably explains why we
have not got 'em.)
> The data type for such a literal is somewhat "undefined":
> It is implementation-defined whether the declared type of a string literal>
> is a fixed-length binary string type, a variable-length binary string
> type,
> or a binary large object string type
Hm, we think it's bit(N):
regression=# select pg_typeof(x'1234abcdf');
pg_typeof
---
bit
(1 row)
which is what the SQL standard *used* to say, before they unceremoniously
threw the bit types under the bus and replaced them with "binary strings".
I wonder how painful it would be to update that stuff to be compatible
with more recent versions of the standard. Or whether anyone really
cares --- people doing this in PG seem to be happy enough with bytea.
regards, tom lane
--
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] Enum in foreign table: error and correct way to handle.
Peter Swartz writes: > suppose the foreign database adds a value to the enum, and the foreign > table now has rows with this new value, while the local definition of the > enum remains unchanged. Obviously, the appropriate action on my part is to > maintain consistency of enum definition between the foreign and local > database, but I'm curious about what behavior would result if there was an > error in this manual updating process. What I'd expect to happen is that you'd get errors when retrieving rows that had the values not known on the local side. One potential way around this is to declare the foreign table's columns as "text" rather than enums; you would lose some error checking on the local side, but the remote server would enforce validity whenever you stored something. (But I'm not sure whether this hack behaves desirably for WHERE conditions on the enum column; you'd need to test.) regards, tom lane -- 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] Server tries to read a different config file than it is supposed to
Adrian Klaver writes: > On 05/23/2015 08:06 AM, Tom Lane wrote: >> I can't explain that claim, but for me, -c config_file=something >> seems to work as you'd expect, and a look at the server source >> code confirms that it should honor that (cf SelectConfigFiles()). >> I think the documentation Adrian pointed to is a bit out of date, >> or at least oversimplified. > So order on the the command line is not important, the start up code > sets its own precedence? Order on the command line would only matter if you wrote -c config_file twice. The point here is that an explicit setting for config_file on the command line overrides the default based on -D. Or at least it's supposed to. I'm not sure now what's going wrong for the OP. regards, tom lane -- 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] Re: Server tries to read a different config file than it is supposed to
Adrian Klaver writes: > So on my Ubuntu installs it does not set up the postgres user to allow login, > so how are you getting to: > su postgres -c ... >From root, presumably ... I thought of a different theory: maybe the server's complaint is not due to trying to read that file as a config file, but it's just because there is an unreadable/unwritable file in the data directory. See Christoph Berg's complaint at http://www.postgresql.org/message-id/20150523172627.ga24...@msg.df7cb.de This would only apply if the OP was trying to use this week's releases though. Also, I thought the fsync-everything code would only run if the server had been shut down uncleanly. Which maybe it was, but that bit of info wasn't provided either. regards, tom lane -- 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] Trying to solve a SocketError [Errno 13] Permission with PL/Python in PostgreSQL 9.3
Marcos Ortiz writes:
> O.S: CentOS 7 64 bits
> We are working here to integrate PostgreSQL with Neo4j through PL/Python
> using the py2neo module for it, and when we want to send sentences to
> Neo4j using port 7474, the executed code raises a SocketError [Errno 13]
> Permission denied.
> I tested the same code in a normal python script outside of PostgreSQL,
> and it works well, but the problem is when I use the code inside
> PostgreSQL with PL/Python.
Probably SELinux is set up to deny random connections originating from the
postgresql daemon. If disabling SELinux makes the problem go away then
that's it. (I do *not* recommend that as a permanent solution, of course.
You'll want to find some finer-grained change to the security policy.
Don't remember enough about SELinux to know what the most likely bet is.)
regards, tom lane
--
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 and undo logging
Ravi Krishna writes:
> Thanks for the detailed explanation. The explanation makes me wonder
> that PG must do more work at commit time, right?
No. Commit and abort are both O(1). Where we pay the piper is in
having to run VACUUM to clean up no-longer-needed row versions.
This is a better design in principle, because the necessary maintenance
can be done in background processes rather than making clients wait
for transactions to finish. In practice, it's still pretty annoying,
just in different ways than Oracle's UNDO.
regards, tom lane
--
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] Re: Server tries to read a different config file than it is supposed to
twoflower writes: > I was doing this after I upgraded to 9.4.2, yes. As for the shut down: I > suspect the server was rebooted without explicitly stopping Postgres. Not > sure how this plays out in terms of cleanliness. This is everything relevant > in the log file after I ran the start script: > 2015-05-23 10:36:39.999 GMT [2102][0]: [1] LOG: database system was > interrupted; last known up at 2015-05-23 08:59:41 GMT > 2015-05-23 10:36:40.053 GMT [2102][0]: [2] FATAL: could not open file > "/storage/postgresql/9.4/data/postgresql.conf": Permission denied > 2015-05-23 10:36:40.054 GMT [2100][0]: [3] LOG: startup process (PID 2102) > exited with exit code 1 > 2015-05-23 10:36:40.054 GMT [2100][0]: [4] LOG: aborting startup due to > startup process failure Yeah, so this is long after the real config file has been read. I think that that unwritable postgresql.conf file had probably been hanging around in your data directory for some time. It was not causing any particular problem until we decided we ought to fsync everything in the data directory after a crash. So this is indeed the same case Christoph was complaining about. But really you should remove that file not just change its permissions; as is it's just causing confusion. > I also tried the same situation on two other Ubuntu servers with the same > version of Postgres (also upgraded to 9.4.2) and the same directory layout - > made *postgresql.conf* in the data directory unaccessible, even renamed it, > and everything worked fine. The only difference is that these are > streaming-replicated standby servers. They also had been restarted without > explicitly terminating Postgres. Hm. I wonder why we aren't fsync'ing on crash restart on standby servers as well. regards, tom lane -- 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] duplicate key value violates unique constraint "pg_class_relname_nsp_index"
Adrian Klaver writes: > On 05/27/2015 06:05 AM, Melvin Davidson wrote: >> What this indicates is that someone, or some thing, is trying to create >> a table in a schema that already exists. > The error you see in that situation is: > postgres-2015-05-27 06:25:10.173 PDT-0ERROR: relation "table1" already > exists > postgres-2015-05-27 06:25:10.173 PDT-0STATEMENT: create table table1 (i > int); > Best guess is as Pete and Albe said, some user code is directly > accessing pg_class or the index has been corrupted. I don't think it's necessary to make such assumptions to explain the errors. What is more likely is that two sessions are trying to create identically named tables at about the same time. You do get the nice user-friendly "already exists" error if the conflicting table was committed before CREATE TABLE looks --- but in a concurrent-transactions situation, neither CREATE TABLE will see the other's table as already existing. In race conditions like this, it's the unique index on the catalog that is the duplicate-preventer of last resort, and it's going to throw this error. regards, tom lane -- 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 not working - need different constraints?
"=?utf-8?b?RG91ZyBHb3JsZXk=?=" writes:
> I'm attempting to implement table partitioning with constraint exclusions,
> but I'm not seeing the planner limit the plan to an appropriate set of
> child tables. I'm wondering if the functions in my constraints are making
> the exclusion impossible.
> My constraints look like this:
> Â Â Â ALTER TABLE response_data.queries_response_2015w23
> Â Â Â Â Â ADD CONSTRAINT queries_response_2015w23_timestamp_check
> Â Â Â Â Â Â Â Â Â CHECK (
> Â Â Â Â Â Â Â Â Â Â Â Â date_part('year'::text, "timestamp"::timestamp
> without time zone) = 2015::double precision AND
> Â Â Â Â Â Â Â Â Â Â Â Â date_part('week'::text, "timestamp"::timestamp
> without time zone) = 23::double precision
> Â Â Â Â Â Â Â Â Â );
> And my query looks like this:
> Â Â Â explain select * from public.queries_response where
> age("timestamp"::timestamp) < '24 hours';
> Â Â Â
> http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html gives this
> caveat:
> Â Â Â "Constraint exclusion only works when the query's WHERE clause contains
> Â Â Â constants (or externally supplied parameters). For example, a
> comparison
> Â Â Â against a non-immutable function such as CURRENT_TIMESTAMP cannot be
> Â Â Â optimized, since the planner cannot know which partition the function
> Â Â Â value might fall into at run time."
> Â Â Â
> Will I likely need to replace the date_part functions with actual dates to
> make this work?
Well, you can't make it work like that, for sure. The planner has no clue
that there's any connection between age() and date_part(). And if it did
fully understand that relationship, it still would refuse to do constraint
exclusion in this example, because the age() constraint is
current-timestamp-dependent. It couldn't assume that now() when executing
the query is the same as it was when planning the query, so it wouldn't
know which partition to select.
Worse still, if I'm right in guessing that the timestamp column is
timestamp WITH time zone (if it isn't, why are you bothering with the
casts?) then the check constraints themselves aren't immutable, because
their effective behavior depends on the current setting of TimeZone.
So the planner will refuse to make any deductions at all with them.
You'd be much better off using child-table constraints like
"timestamp" >= '2015-01-01' AND "timestamp" < '2015-01-08'
because the planner can reason about them. But I'm afraid the age()
technique still loses.
regards, tom lane
--
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 not working - need different constraints?
"=?utf-8?b?RG91ZyBHb3JsZXk=?=" writes:
> On Wed, 27 May 2015 18:21:58 -0400, Tom Lane wrote:
> You'd be much better off using child-table constraints like
> "timestamp" >= '2015-01-01' AND "timestamp" < '2015-01-08'
> because the planner can reason about them. But I'm afraid the age()
> technique still loses.
> Thanks Tom, I will re-work the constraints to use static dates.
> Â
> If I do that, will I be able to use age() (or some other technique) to
> apply constraint exclusion when running a query asking, "show me
> all records where the timestamp is within the last 24 hours"?
Basically the query will need to look like
WHERE "timestamp" >= 'timestamp-constant'
or the planner won't be able to eliminate any partitions.
People have occasionally resorted to lying to the planner in order to get
this result without doing timestamp arithmetic on the client side. That
is, something like
create function ago(interval) returns timestamp as
'select localtimestamp - $1'
language sql immutable;
select ... where "timestamp" >= ago('24 hours');
Labeling this function immutable is a plain lie, and if you use it in any
context other than an interactive query submitted for immediate execution,
you'll deserve the pain you'll get ;-). But within that context, it's just
what you want that the function gets folded to a constant immediately;
that happens soon enough that the WHERE clause looks like "timestamp" >=
'timestamp-constant' for the purposes of constraint exclusion.
regards, tom lane
--
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] Partitioning and performance
Ravi Krishna writes:
> Perhaps I was not clear. The planner is excluding partitions which can
> not contain the rows looked up in the WHERE clause. However it is
> still scanning the parent table.
Sure, because you don't have a constraint forbidding the parent from
having a matching row, no?
In older versions of PG there wasn't any way around this, but recent
versions allow you to mark a constraint as NO INHERIT, which would
let you attach such a constraint to the parent only.
By and large, though, this doesn't really matter, since an empty
parent table won't cost anything much to scan. If it's significant
relative to the child table access time then you probably didn't
need partitioning in the first place.
regards, tom lane
--
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] Partitioning and performance
Ravi Krishna writes:
> Is there a rule of thumb as to at what size does the partitioning
> start performing better than non partitioned table.
Personally I'd not worry about partitioning until I had a table
approaching maybe a billion (1e9) rows. You could argue that
an order of magnitude either way, but it's just not worth the
extra complexity for data volumes very much less than that.
regards, tom lane
--
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] Partitioning and performance
Ravi Krishna writes: > So cost wise they both look same, still when i run the sql in a loop > in large numbers, it takes rougly 1.8 to 2 times more than non > partitioned table. If you're testing cases that only involve fetching a single row, the discrepancy could well be down to extra planning time. Proving that the other partitions don't need to be scanned is far from free. It's also worth realizing that for queries that fetch just one or a few rows, it's very unlikely that partitioning can beat an unpartitioned table, period. Basically, partitioning replaces a runtime search of the top levels of a large index with a plan-time proof that other partitions need not be visited. That is not going to be cheaper and could well be a lot more expensive. The situations where partitioning is useful boil down to: 1. You have repetitive, stylized requirements such as "every month, delete all data older than X months" that can be mapped to "drop the oldest partition" instead of doing an expensive table scan. 2. You can arrange things so that certain partitions are accessed far more often than others, thus directing most disk traffic to specific child tables that will remain in RAM cache most of the time. (In principle, you could get similar cache-friendly behavior from a clustered unpartitioned table, but it's usually too hard to ensure that such a table stays clustered.) It does not sound like your test case is exercising either of those win scenarios, and all you're measuring is the overhead of partitioning, which as I said is substantial. regards, tom lane -- 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] Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Thomas Munro writes:
> On Fri, May 29, 2015 at 11:24 AM, Robert Haas wrote:
>> B. We need to change find_multixact_start() to fail softly.
> Here is an experimental WIP patch that changes StartupMultiXact and
> SetMultiXactIdLimit to find the oldest multixact that exists on disk
> (by scanning the directory), and uses that if it is more recent than
> the oldestMultiXactId from shmem,
Not sure about the details of this patch, but I was planning to propose
what I think is the same thing: the way to make find_multixact_start()
fail softly is to have it find the oldest actually existing file if the
one that should be there isn't.
This would preserve the idea that we aren't trusting the multixact
tracking data to be completely right, which was the point of 78db307bb
and which evidently is still essential. It would also obviate the need
for other places to contain similar logic.
regards, tom lane
--
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 sorting the results of a join, without denormalization
"Glen M. Witherington" writes: > And here's the query I want to do, efficiently: > SELECT * FROM c > JOIN b ON b.id = c.b_id > JOIN a ON a.id = b.a_id > WHERE a.id = 3 > ORDER BY b.created_at DESC > LIMIT 10 At least for that dummy data, this seems sufficient: regression=# create index on b (a_id, created_at); CREATE INDEX regression=# explain analyze SELECT * FROM c JOIN b ON b.id = c.b_id JOIN a ON a.id = b.a_id WHERE a.id = 3 ORDER BY b.created_at DESC LIMIT 10; QUERY PLAN -- Limit (cost=0.14..21.95 rows=10 width=64) (actual time=0.064..1.176 rows=10 loops=1) -> Nested Loop (cost=0.14..436079.81 rows=20 width=64) (actual time=0.063..1.173 rows=10 loops=1) Join Filter: (b.id = c.b_id) Rows Removed by Join Filter: 1218 -> Nested Loop (cost=0.14..9.81 rows=20 width=40) (actual time=0.035..0.035 rows=1 loops=1) -> Index Scan Backward using b_a_id_created_at_idx on b (cost=0.14..8.49 rows=20 width=24) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (a_id = 3) -> Materialize (cost=0.00..1.07 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1) -> Seq Scan on a (cost=0.00..1.06 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1) Filter: (id = 3) Rows Removed by Filter: 2 -> Materialize (cost=0.00..27230.00 rows=100 width=24) (actual time=0.008..0.811 rows=1228 loops=1) -> Seq Scan on c (cost=0.00..16370.00 rows=100 width=24) (actual time=0.007..0.310 rows=1228 loops=1) Planning time: 0.796 ms Execution time: 1.390 ms (15 rows) regards, tom lane -- 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] Curious case of huge simple btree indexes bloat.
Maxim Boguk writes: > On the one of databases under my support I found very curious case of the > almost endless index bloat (index size stabilises around 100x of the > original size). > The table have 5 indexes and they all have the same bloating behaviour > (growth to almost 100x and stabilisation around that amount). An original > index size 4-8Mb (after manual reindex), over time of the 5 days they all > monotonically growth to 300-900MB. In the same time table size staying > pretty constant at 30-50Mb (and amount of rows in the same don't vary > widely and stays between 200k and 500k). At least for the index you gave stats for, it seems like it's stabilizing at one index entry per page. This is a known possible pathological behavior if the application's usage involves heavy decimation of original entries; say, you insert sequential timestamps and then later remove all but every one-thousandth one, leaving at most one live entry on every index page. Btree can recover the totally-empty leaf pages but it has no provision for merging non-empty leaf pages, so those all stay as they are indefinitely. It would be pretty unusual for all the indexes on a table to be used like that, though. regards, tom lane -- 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] Find out what on what function depends an index
Andreas Ulbrich writes:
> The question is the todo in the script: Is there a way to find out what
> indexes depends on what function?
Direct dependencies would show up in pg_depend. Indirect ones wouldn't,
since we don't analyze function bodies to see what they call (and if we
tried, there's the little matter of the halting problem).
A larger problem is that frequently the real issue with an unstable index
expression definition is that it depends on context, such as GUC settings,
rather than the function body per se. So I'm not sure how much pain you'd
really be able to prevent with this approach.
regards, tom lane
--
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] postgres db permissions
Adrian Klaver writes: > On 06/02/2015 11:04 AM, Steve Pribyl wrote: >> I have noted that "GRANT ALL ON SCHEMA public TO public" is granted >> on postgres.schemas.public. I am looking at this in pgadmin so excuse >> my nomenclature. >> Is this what is allowing write access to the database? > Yes, though that should not be the default. Huh? Of course it's the default. I'm not really sure why the OP is surprised at this. A database that won't let you create any tables is not terribly useful. If you don't like this, you can get rid of the database's public schema and/or restrict who has CREATE permissions on it. But I can't see us shipping a default configuration in which only superusers can create tables. That would just encourage people to operate as superusers, which overall would be much less secure. regards, tom lane -- 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_relation_size performance issue
Hans Guijt writes:
> I have a Postgres 9.3.7 database, freshly created on Ubuntu 14 LTS 64 bit,
> and at this time almost completely empty. I'm attempting to find the size of
> a table, using the following code:
> SELECT
> pg_relation_size (stat.relid),
> CASE WHEN cl.reltoastrelid = 0 THEN
> 0
> ELSE
> pg_relation_size (cl.reltoastrelid) + COALESCE ((
> SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index WHERE
> indrelid=cl.reltoastrelid
> ), 0)::int8
> END,
> COALESCE ((SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index WHERE
> indrelid=stat.relid), 0)::int8
> FROM pg_stat_all_tables stat
> JOIN pg_class cl ON cl.oid=stat.relid
> JOIN pg_namespace ns ON cl.relnamespace=ns.oid
> WHERE UPPER (cl.relname) = UPPER ('sensor')
>AND UPPER (ns.nspname) = UPPER ('devtest')
Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.
regards, tom lane
--
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] Row visibility issue with consecutive triggers, one being DEFERRED
Marc Mamin writes:
> The test below is running fine
> but if you add the trigger push_foo_tr (uncomment)
> then the exception is raised.
Doesn't that trigger result in infinite recursion?
> CREATE OR REPLACE FUNCTION push_foo_trf () returns trigger AS $$
> BEGIN
> UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id;
> RETURN NEW;
> END; $$ language plpgsql;
> --CREATE TRIGGER push_foo_tr
> -- AFTER UPDATE ON foo
> -- FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();
AFAICS, each trigger firing would re-queue another one because of
the fresh UPDATE.
regards, tom lane
--
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] cannot connect to database through ECPG but can through psql
Seann Reed writes: > I'm a relative newbie to Postgres but I've asked a few colleagues about > this problem without a solution, so I'm hoping someone on the list serve > can help. > The problem arose when I move to an upgraded operating system, an upgraded > version of Postgres, and when I dump a database out of the old machine and > restore it on a new machine. > Old: RedHat 5.9, Postgres 8.1.23 > New: RedHat 6.6, Postgres 9.2 > Everything worked fine with the old setup. With the new setup, I can > access my database through psql command-line. However, I have a compiled > program called 'Shefdecode' that makes queries to the database through ECPG > and this program cannot access the database. I get errors: > "Postgres error -220" and > "SQL STATE = 08003" (User manual says this means "The program tried to > access a connection that does not exist.") > Although I don't have the source for the Shefdecode program, I know it uses > the environment variables PGUSER and PGHOST and also gets passed the > database name. I've checked to make sure these are correct. > What else should I look for to try to determine the cause of this error? Postmaster's log might offer some insight, especially if you turn on log_connections. regards, tom lane -- 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] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Robert Haas writes:
> On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote:
>> I read through this version and found nothing to change. I encourage other
>> hackers to study the patch, though. The surrounding code is challenging.
> Andres tested this and discovered that my changes to
> find_multixact_start() were far more creative than intended.
> Committed and back-patched with a trivial fix for that stupidity and a
> novel-length explanation of the changes.
So where are we on this? Are we ready to schedule a new set of
back-branch releases? If not, what issues remain to be looked at?
regards, tom lane
--
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] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Robert Haas writes: > On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund wrote: >> On 2015-06-05 11:43:45 -0400, Tom Lane wrote: >>> So where are we on this? Are we ready to schedule a new set of >>> back-branch releases? If not, what issues remain to be looked at? >> We're currently still doing bad things while the database is in an >> inconsistent state (i.e. read from SLRUs and truncate based on the >> results of that). It's quite easy to reproduce base backup startup >> failures. >> >> On the other hand, that's not new. And the fix requires, afaics, a new >> type of WAL record (issued very infrequently). I'll post a first version >> of the patch, rebased ontop of Robert's commit, tonight or tomorrow. I >> guess we can then decide what we'd like to do. > There are at least two other known issues that seem like they should > be fixed before we release: > 1. The problem that we might truncate an SLRU members page away when > it's in the buffers, but not drop it from the buffers, leading to a > failure when we try to write it later. > 2. Thomas's bug fix for another longstanding but that occurs when you > run his checkpoint-segment-boundary.sh script. > I think we might want to try to fix one or both of those before > cutting a new release. I'm less sold on the idea of installing > WAL-logging in this minor release. That probably needs to be done, > but right now we've got stuff that worked in early 9.3.X release and > is now broken, and I'm in favor of fixing that first. Okay, but if we're not committing today to a release wrap on Monday, I don't see it happening till after PGCon. regards, tom lane -- 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] alter column type
Casey Deccio writes:
> Being unfamiliar with the internals, what's the risk here? If postgres
> thinks something is a bigint, but previously stored it as an int, does that
> mean it will try to extract data beyond the boundary of some of the (old)
> 32-bit values and potentially throw off offsets for other values?
Yes. This *will* break your table, spectacularly. The other person who
was opining that it would work has no understanding of the actual storage
layout.
regards, tom lane
--
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] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Andres Freund writes: > On June 5, 2015 10:02:37 PM GMT+02:00, Robert Haas > wrote: >> I think we would be foolish to rush that part into the tree. We >> probably got here in the first place by rushing the last round of >> fixes too much; let's try not to double down on that mistake. > My problem with that approach is that I think the code has gotten > significantly more complex in the least few weeks. I have very little trust > that the interactions between vacuum, the deferred truncations in the > checkpointer, the state management in shared memory and recovery are correct. > There's just too many non-local subtleties here. > I don't know what the right thing to do here is. My gut feeling is that rushing to make a release date is the wrong thing. If we have confidence that we can ship something on Monday that is materially more trustworthy than the current releases, then let's aim to do that; but let's ship only patches we are confident in. We can do another set of releases later that incorporate additional fixes. (As some wise man once said, there's always another bug.) If what you're saying is that you don't trust the already-committed patch very much, then maybe we'd better hold off another couple weeks for more review and testing. regards, tom lane -- 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 count(*);
Marc Mamin writes: >> The * might be a bit tricky, though, >> since 'select *;' doesn't work. > That's the point. * has no meaning without FROM PG regards "count(*)" as a weird spelling of "count()", ie, invoke an aggregate that takes no arguments. It really doesn't have anything to do with the meaning of "*" as a SELECT-list item, any more than it does with the meaning of "*" as multiplication, say. It's just syntax that's written like that because the SQL standard says we have to. Another way of making the point is that SELECT without a FROM list can be seen as implicitly selecting from a dummy table with one row and no columns. Some other systems such as Oracle make you do that explicitly, ie the infamous "FROM dual" hack; there's nothing in the SQL standard saying you can omit FROM. If you suppose that that's the underlying model then the result of "select count(*)" is completely natural. regards, tom lane -- 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-level lockdown
Filipe Pina writes:
> It will try 5 times to execute each instruction (in case of
> OperationError) and in the last one it will raise the last error it
> received, aborting.
> Now my problem is that aborting for the last try (on a restartable
> error - OperationalError code 40001) is not an option... It simply
> needs to get through, locking whatever other processes and queries it
> needs.
I think you need to reconsider your objectives. What if two or more
transactions are repeatedly failing and retrying, perhaps because they
conflict? They can't all forcibly win.
regards, tom lane
--
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] GCC error and libmpfr.so.4 not found
Asma Riyaz writes:
> in bashrc:-
> LD_LIBRARY_PATH=/seq/annotation/bio_tools/BOOST/boost_1_46_1/lib
> LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/seq/regev_genome_portal/lib
If you didn't have an "export LD_LIBRARY_PATH" in there, I don't
think this would do anything.
But your real problem is you have a broken gcc installation. Basic
tools like that should not need a custom LD_LIBRARY_PATH to work.
regards, tom lane
--
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] Alter column from text[] to uuid[]
Keith Rarick writes: > I recently did the following: > kr=# alter table t alter u type text[]; > ALTER TABLE > Time: 5.513 ms > Now I'd like to put it back the way it was, but my attempts didn't work: > kr=# alter table t alter u type uuid[]; > ERROR: column "u" cannot be cast automatically to type uuid[] > HINT: Specify a USING expression to perform the conversion. > Time: 0.244 ms It wants you to do this: alter table t alter u type uuid[] using u::uuid[]; The original command worked without a USING because anything-to-text is considered an allowable assignment coercion; but the other way around requires an explicit cast. > kr=# alter table t alter u type uuid[] using array_to_string(u,','); > ERROR: column "u" cannot be cast automatically to type uuid[] > HINT: Specify a USING expression to perform the conversion. > Time: 0.321 ms > (Interestingly, postgres seems to think I don't even have a USING clause > here. Could there be some optimization that removed it?) No, the error message is just worded carelessly; it's the same whether or not you said USING. Probably when there's a USING it needs to be worded more like ERROR: result of USING clause cannot be cast automatically to type uuid[] regards, tom lane -- 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] localtime ?
James Cloos writes:
> On one of my servers, even thopugh everything is in UTC, pg insists on
> using timezone -04 by default.
> | cloos=# SELECT current_setting('TIMEZONE');
> | current_setting
> | -
> | localtime
> | (1 row)
AFAIK, that is not a valid value for timezone, unless someone has stuck a
file by that name into your zoneinfo database directory (which I think is
standard practice on some distros though by no means all). If so, it
would mean whatever the file said, which would very likely not be UTC.
> But:
> | :; grep timezone /etc/postgresql/9.3/main/*
> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
Evidently that grep has little to do with your actual configuration
source. This would likely be informative as to where "localtime"
is coming from:
select * from pg_settings where name = 'TimeZone';
regards, tom lane
--
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] support for ltree
Michael Shapiro writes:
> Thanks for this response. It still leave my question unanswered. I should
> rephrase it -- will become a native datatype in Postgres (as
> opposed to remaining an extension). Are there any plans to make a
> native datatype?
No. That is not the same as it being unsupported. Postgres is built
around the concept of being extensible, and one important aspect of that
is having some "standard" extensions as test cases. ltree seems to fit
that category quite well, in that it's useful for some people but not so
widely used as to need to be in core.
(Even if there were a credible argument for putting ltree in core, I doubt
it would win out over backwards-compatibility concerns. We've found in
the past that moving things into core is not exactly transparent.)
regards, tom lane
--
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] double precision[] storage space questions
greg writes:
> I cannot find any documentation on the space taken by a double precision
> array. And the few tests I did surprise me.
> Here are a few tries I did to understand
> select pg_column_size(1.1::double precision) return 8--- as
> expected
> select pg_column_size('{}'::double precision[]) return 16 --- ok
> maybe an array header
> select pg_column_size('{1.111}'::double precision[]) return 32 --- I
> expected 16+ sizeof(double) = 24
'{}' is a zero-dimensional array so it doesn't have the same
dimensionality information that your third case does. See
the comments at the head of
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/utils/array.h;hb=HEAD
> The whole point is that in the application I work on, we store double arrays
> as bytea (using some serialization before storing the data).
TBH, that seems like a pretty silly decision. It guarantees that you
cannot do any useful manipulations of the array on the database side.
regards, tom lane
--
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] Row count estimation bug in BETWEEN?
=?koi8-r?B?/cXLyc4g8dLP08zB1w==?= writes: > I've noticed strange row count estimations in BETWEEN predicate: > EXPLAIN > SELECT * > FROM t1 > WHERE f1 = 42; > -->> Bitmap Heap Scan on t1 (cost=203.07..28792.94 rows=10662 width=24) > > EXPLAIN > SELECT * > FROM t1 > WHERE f1 BETWEEN 42 AND 42; > -->> Index Scan using table1_field1_idx on t1 (cost=0.44..8.46 rows=1 > width=24) > Why row count estimations for two logically equivalent queries are so > different? PG doesn't try to estimate inequalities exactly, because it usually doesn't make enough of a difference to matter. Currently we don't even bother to distinguish say ">" from ">=" for estimation purposes, though certainly we would need to in order to deal with zero-width ranges with any great amount of precision. regards, tom lane -- 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] Row count estimation bug in BETWEEN?
Yaroslav writes: > Tom Lane-2 wrote >> PG doesn't try to estimate inequalities exactly, because it usually >> doesn't make enough of a difference to matter. Currently we don't >> even bother to distinguish say ">" from ">=" for estimation purposes, >> though certainly we would need to in order to deal with zero-width ranges >> with any great amount of precision. > Thank you for your answer! > I'm sorry, but after looking into documentation and sources > (scalarineqsel function in selfuncs.c, clauselist_selectivity and > addRangeClause functions in clausesel.c) and experimenting a little I've > got an impression that PostgreSQL actually bothers to distinguish ">" > from ">=" for estimation purposes sometimes (probably, when MCV is > used), but in my example it uses histogram and indeed doesn't > distinguish them. Well, I was oversimplifying a bit. When testing the MCV list we use the original operator, so that if the comparison constant is equal to some MCV entry, it will indeed matter whether you said ">" or ">=". When dealing with the histogram, however, we don't pay attention to the difference. The assumption is that the histogram represents a continuous distribution of values in which no one value occurs often enough to be interesting (if it did, it would be in the MCV list...). Therefore it does not matter much whether any specific histogram entry is exactly "=". And of course, for comparison values that are between histogram entries, we have no idea whatsoever whether there are any "=" entries in the table; so even if the code did distinguish ">" from ">=", it would be unclear what to do with the knowledge. regards, tom lane -- 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 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Douglas Stetner writes:
> Looking for confirmation there is an issue with pg_dump failing after
> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux.
Quick thought --- did you restart the Postgres service after upgrading
openssl? If not, your server is still using the old library version,
while pg_dump would be running the new version on the client side.
I don't know exactly what was done to openssl in the last round of
revisions, but maybe there is some sort of version compatibility issue.
Also, you really ought to be running something newer than PG 8.4.9.
regards, tom lane
--
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_class.reltuples VS pg_stat_all_tables.n_live_tup for estimation of table
William Dunn writes: > Does anyone which is a more accurate estimate of a table's live > rows: pg_class.reltuples ( > http://www.postgresql.org/docs/current/static/catalog-pg-class.html) > OR pg_stat_all_tables.n_live_tup ( > http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW)? They're both inaccurate with different sources of inaccuracy. I dunno that you should assume that one is necessarily better than the other. >- Is pg_class.reltuples an estimation of live tuples only, or is it of >all tuples (both live and dead)? I would guess it's live only but that is a >guess Hm. I'm pretty sure the planner takes it as counting live tuples only, but it looks like VACUUM thinks it includes recently-dead-but-not-yet- removable tuples. We might need to do some adjustment there. regards, tom lane -- 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] PL/pgSQL: How to return two columns and multiple rows
"Raymond O'Donnell" writes:
> On 18/06/2015 13:36, Sven Geggus wrote:
>> I would like to be able to do something like this:
>>
>> select myfunc('foo','bar');
>> or
>> select myfunc(foo, bar) from foobartable;
>> or even
>> select myfunc(foo, bar), 'baz' as baz from foobartable;
> You need to do:
>select * from myfunc('foo','bar');
That's enough to expand the output from a simple function call. If you
want to do something like Sven's later examples, the best way is with
LATERAL:
select f.*, 'baz' as baz from foobartable, lateral myfunc(foo, bar) as f;
regards, tom lane
--
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] writable cte triggers reverse order
=?UTF-8?B?0JzQuNGI0LAg0KLRjtGA0LjQvQ==?= writes:
> for me case listed below looks like something goes wrong (at least very
> strange)
> ...
> see on order of events -- INSERTs come first before DELETEs!
The reason for this is that the AFTER INSERT trigger events are queued
within the INSERT statements executed by the test_trg_inh_func function,
and then executed at the completions of those INSERT statements. On
the other hand, the AFTER DELETE trigger events are queued by the outer
WITH ... DELETE ... INSERT statement, so they fire at the completion
of that statement.
You could probably get the behavior you want if you make all the triggers
DEFERRED so that they all fire at end-of-transaction, rather than at end
of the statement that queued them.
regards, tom lane
--
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 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Douglas Stetner writes: > On 18 Jun 2015, at 02:06 , Tom Lane wrote: >> Douglas Stetner writes: >>> Looking for confirmation there is an issue with pg_dump failing after >>> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux. >> Quick thought --- did you restart the Postgres service after upgrading >> openssl? If not, your server is still using the old library version, >> while pg_dump would be running the new version on the client side. >> I don't know exactly what was done to openssl in the last round of >> revisions, but maybe there is some sort of version compatibility issue. >> >> Also, you really ought to be running something newer than PG 8.4.9. > Thanks for the reply Tom. Unfortunately restart did not help. Will try > an upgrade to 8.4.20 (other software depends on 8.4.x) A remote client > with 8.4.20 works, so fingers crossed. Hm. The only possibly SSL-relevant patch I see in the 8.4 git history is this: Author: Tom Lane Branch: master Release: REL9_4_BR [74242c23c] 2013-12-05 12:48:28 -0500 Branch: REL9_3_STABLE Release: REL9_3_3 [2a6e1a554] 2013-12-05 12:48:31 -0500 Branch: REL9_2_STABLE Release: REL9_2_7 [41042970b] 2013-12-05 12:48:35 -0500 Branch: REL9_1_STABLE Release: REL9_1_12 [ad910ccdc] 2013-12-05 12:48:37 -0500 Branch: REL9_0_STABLE Release: REL9_0_16 [36352ceb4] 2013-12-05 12:48:41 -0500 Branch: REL8_4_STABLE Release: REL8_4_20 [7635dae55] 2013-12-05 12:48:44 -0500 Clear retry flags properly in replacement OpenSSL sock_write function. Current OpenSSL code includes a BIO_clear_retry_flags() step in the sock_write() function. Either we failed to copy the code correctly, or they added this since we copied it. In any case, lack of the clear step appears to be the cause of the server lockup after connection loss reported in bug #8647 from Valentine Gogichashvili. Assume that this is correct coding for all OpenSSL versions, and hence back-patch to all supported branches. Diagnosis and patch by Alexander Kukushkin. Although the problem that was reported at the time isn't much like yours, it's possible that this missing step has additional effects with the latest openssl version; so it's certainly worth trying. Whether this fixes your immediate issue or not, you really ought to be using the last available 8.4.x version, which is 8.4.22. regards, tom lane -- 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] Postgres SIGALRM timer
Scottix writes: > I am running a posgres server with a zabbix server and zabbix agent and I > am getting a bunch errors about SIGALRM, It makes postgres drop connections > randomly. > 2015-06-19 08:45:22 PDT FATAL: could not enable SIGALRM timer: Invalid > argument That's really weird. [ looks at code... ] You aren't using a --disable-integer-timestamps build by any chance, are you? Even if you were, I don't see how we could be passing a bad value to setitimer(). I'm a bit inclined to say there's something wrong with your kernel, since we've not heard similar reports elsewhere. regards, tom lane -- 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] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
"David G. Johnston" writes: > I know this could be written quite easily in sql but was wondering if it is > possible in pl/pgsql. > CREATE FUNCTION test_func() > RETURNS text > LANGUAGE 'plpgsql' > AS $$ > BEGIN > SELECT 'text_to_return' INTO ; --with or without a cast > RETURN ; > END; > $$; > The goal is to return the value of text_to_return without declaring an > explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use "RETURN expression" if this is all that will be in the function. regards, tom lane -- 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] Less is More
Paul Ramsey writes: > I have an odd performance quirk I wonder if anyone has any theories for⦠> (not a one-time result, table all heated up, etc) > spain=# explain analyze select way from planet_osm_point;        >                      QUERY PLAN > - >  Seq Scan on planet_osm_point  (cost=0.00..37240.97 rows=1748797 width=32) > (actual time=0.044..813.521 rows=1748797 loops=1) >  Total runtime: 902.256 ms > (2 rows) > Time: 902.690 ms > spain=# explain analyze select * from planet_osm_point; >                           >    QUERY PLAN > -- >  Seq Scan on planet_osm_point  (cost=0.00..37240.97 rows=1748797 > width=848) (actual time=0.051..241.413 rows=1748797 loops=1) >  Total runtime: 330.271 ms > (2 rows) > Time: 331.869 ms "SELECT *" gets to skip the projection step that is usually needed to remove unwanted columns from the query result. Note that neither of these numbers have much to do with the real time to execute the query and return results to the client. EXPLAIN ANALYZE doesn't bother to convert the query results to text, much less do any network I/O. regards, tom lane -- 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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Albe Laurenz writes: > Piotr Gackiewicz wrote: >>> Douglas Stetner writes: >>>> Looking for confirmation there is an issue with pg_dump failing after >>>> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux. >> I have the same problem with fresh postgresql 9.2.13. >> Started after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 >> >> Since then pg_dump aborts after dumping circa 2GB: >> pg_dump: [archiver (db)] query failed: SSL error: unexpected message >> pg_dump: [archiver (db)] query was: FETCH 100 FROM _pg_dump_cursor I've been able to reproduce this failure with Postgres HEAD, so whatever it is, it's pretty much independent of our code version. It was fine with openssl-1.0.1e-30.el6_6.9.x86_64 but after updating to openssl-1.0.1e-30.el6_6.11.x86_64 pg_dump fails after about 2GB worth of data transfer. I find that setting ssl_renegotiation_limit to 0 in postgresql.conf allows things to work, so it's got something to do with bad renegotiation. But curiously, the amount of data dumped before failing is the same whether ssl_renegotiation_limit is 512MB (the default) or something much smaller such as 10MB. In either case we should have successfully completed several renegotiations before the failure, so I don't think it's solely a matter of "renegotiation is busted". > Maybe it has something to do with this OpenSSL bug: > http://rt.openssl.org/Ticket/Display.html?id=3712&user=guest&pass=guest That link doesn't work for me :-( I'm going to file this as a bug with Red Hat. In the meantime it looks like we can suggest ssl_renegotiation_limit = 0 as a temporary workaround. regards, tom lane -- 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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
I wrote:
> I'm going to file this as a bug with Red Hat. In the meantime it looks
> like we can suggest ssl_renegotiation_limit = 0 as a temporary workaround.
Done at
https://bugzilla.redhat.com/show_bug.cgi?id=1234487
regards, tom lane
--
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] foreign keys to foreign tables
Rick Otten writes: > Hello pgsql-general, > I'd like to set up a foreign key constraint to a foreign table from a local > table. > ie, I have a column in a local table that I'd like to ensure has a value in > the foreign table. > alter mytable > add column some_column_id uuid references myforeigntable(some_column_id) > ; > Unfortunately I get a "not a table" error when I try this. > ERROR: referenced relation "myforeigntable" is not a table > I'm thinking I'll have to write a function that checks for existance of the > ids in the foreign table, and then put a CHECK constraint on using that > function, but I thought I'd as first if there was a better way. What's going to happen when the foreign server decides to delete some rows from its table? regards, tom lane -- 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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
I wrote:
>> I'm going to file this as a bug with Red Hat. In the meantime it looks
>> like we can suggest ssl_renegotiation_limit = 0 as a temporary workaround.
> Done at
> https://bugzilla.redhat.com/show_bug.cgi?id=1234487
BTW, we should not feel too awful, because it seems this same update has
also broken sendmail, mysql, and probably other services. Not for the
same reason, but still ...
Red Hat fell down badly on QA'ing this.
regards, tom lane
--
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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Piotr Gackiewicz writes:
> Yep, that's it :
> $ psql -h localhost -c "SET ssl_renegotiation_limit='3kB'; SELECT
> repeat('0123456789', 1800);"
> SSL error: unexpected message
> connection to server was lost
> psql and server share same openssl library on the same host, of course.
Red Hat have confirmed that this was caused by a faulty openssl security
patch in RHEL6 and RHEL7. They apparently have a fix already, which
I'd expect will ship in a day or two. Keep an eye on the bugzilla entry
I posted upthread for status.
regards, tom lane
--
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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Piotr Gackiewicz writes:
> $ psql -h localhost -c "SET ssl_renegotiation_limit='3kB'; SELECT
> repeat('0123456789', 1800);"
> SSL error: unexpected message
> connection to server was lost
BTW, are you using any nondefault SSL settings? Because I can't reproduce
the failure you show. In my tests, the value of ssl_renegotiation_limit
does not seem to matter, as long as it's not zero. What it looks like
is that if we've forced any renegotiations, then once the server has
transmitted more than 2GB, the next server SSL_read() call fails. The
precise number of previous renegotiations does not matter.
If the above is reproducible for you, there may be more than one bug :-(
regards, tom lane
--
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] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
I wrote:
> Piotr Gackiewicz writes:
>> $ psql -h localhost -c "SET ssl_renegotiation_limit='3kB'; SELECT
>> repeat('0123456789', 1800);"
>> SSL error: unexpected message
>> connection to server was lost
> BTW, are you using any nondefault SSL settings? Because I can't reproduce
> the failure you show.
Oh, scratch that: I do reproduce that in PG <= 9.3, just not in 9.4 or
HEAD. Apparently our renegotiation rewrite in 9.4 affects this.
regards, tom lane
--
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] Include.d and warnings
Andomar writes: > We've set up an include directory in postgresql.conf: > include_dir = 'conf.d' > The include directory has a file that changes the value of > "max_connections". > Now when we reload Postgres, an error appears in the logs: > - > parameter ""max_connections"" cannot be changed without restarting the > server > ... > configuration file ""/var/lib/pgsql/9.4/data/postgresql.conf"" contains > errors; unaffected changes were applied > - > But max_connections did not change before the reload. It is just > overruled in an included configuration file. This is fixed in 9.4 and up. Since it's just cosmetic, we did not back-patch the change. regards, tom lane -- 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] Counting the occurences of a substring within a very large text
Marc Mamin writes:
> I'd like to count the number linebreaks within a string,
> but I get a memory allocation error when using regexp_matches or
> regexp_split_to_table.
Anything involving a regexp is going to have a maximum input string length
of about 256MB, as a result of conversion to pg_wchar format.
regexp_split_to_table(), for instance, does this:
/* convert string to pg_wchar form for matching */
orig_len = VARSIZE_ANY_EXHDR(orig_str);
wide_str = (pg_wchar *) palloc(sizeof(pg_wchar) * (orig_len + 1));
wide_len = pg_mb2wchar_with_len(VARDATA_ANY(orig_str), wide_str, orig_len);
palloc() would complain for requests beyond 1GB, and sizeof(pg_wchar) is
4, so 256MB is the longest orig_str this can handle. (This coding is on
the hairy edge of being a security bug on 32-bit machines, too, but AFAICS
it is okay because there's a factor of 4 daylight between the max possible
input length of 1GB and overflowing uint32.)
In newer branches we could consider using MemoryContextAllocHuge to escape
the 1GB limit on wide_str, but it would take some research to be sure that
everything involved is using suitable datatypes for string indices and so
forth. I'd be a bit worried about the performance implications of
throwing such large strings around, anyway.
regards, tom lane
--
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] Correct place for feature requests
=?UTF-8?B?0JDQu9C10LrRgdC10Lkg0JHQtdGA0LXQttC90Y/Qug==?= writes: > Is there any correct place for feature requests? Starting a thread here is a reasonable way. regards, tom lane -- 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] Foreign data wrappers and indexes on remote side
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes:
> Is there any way to take use of indexes on foreign tables?
> Currently (at least with tds_fdw, that I was testing) the planner just
> does a dumb full sequential scan in all cases.
That would be something to discuss with the author of tds_fdw. It's
mostly on the head of each individual FDW how smart plans for it will be.
regards, tom lane
--
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 for re-index after pg_upgrade
Andreas Joseph Krogh writes:
> In the man-page for pg_upgrade we see this:
> pg_upgrade will require a reindex if:
> * an index is of type hash or GIN
I think that probably refers to some version-specific upgrade
situations; I can't see a reason why it would be true in general.
Bruce, doesn't that doc need improvement?
regards, tom lane
--
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] plpgsql question: select into multiple variables ?
Adrian Klaver writes: > On 06/29/2015 12:07 PM, Day, David wrote: >> What is wrong with my usage of the plpgsql "select into" concept >> I have a function to look into a calendar table to find the first and >> Last weekend date of a month. >> >> create or replace function sys.time_test () >> returns date as >> $$ >> DECLARE >> first_weekend date; >> last_weekend date; >> BEGIN >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, >> last_weekend::date FROM sys.calendar ... > The ::date cast seem to be the problem. Indeed. Here's what's happening: the argument of INTO can basically only be a list of variable names. (Well, they can be qualified field names, but certainly not cast expressions.) And there's this messy legacy syntax rule that says the INTO clause can be anywhere inside the SELECT list. So what happens is the plpgsql parser reads "INTO first_weekend", notes the next token is :: which can't be part of INTO, and drops back to handling the rest of the input as SELECT text. So what you wrote here is equivalent to SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO first_weekend FROM sys.calendar ... which accidentally looks like perfectly valid SELECT syntax. And I think it doesn't complain about "too many output columns" either. So you end up with no reported error and very confusing results. To make this noticeably better, we'd probably have to insist that INTO come at the end of the SELECT list, which would break lots and lots of existing client code ... so I'm not holding my breath. Moral of the story: being user-friendly by accepting sloppy syntax is not an unalloyed win. regards, tom lane -- 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] plpgsql question: select into multiple variables ?
"David G. Johnston" writes: > On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: >> ... So what you wrote here is equivalent to >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO >> first_weekend FROM sys.calendar ... > âDoes it help to recognize the fact that "first_weekend::âdate" is not a > valid identifier name (because it is lacking double-quotes)? No. You're supposing that we *should* reject this case, which is not true given the current rules. As a counterexample consider SELECT INTO x - y FROM foo which per current rules means SELECT - y INTO x FROM foo The only real difference between this and the :: case is that :: doesn't come in a prefix-operator form, but that's an awfully weak reed to hang a cross-language syntax rule on. >> To make this noticeably better, we'd probably have to insist that >> INTO come at the end of the SELECT list, > Are you missing a "not" here? No, I'm not. See previous example. To detect errors more completely, we'd need a rule that what follows the INTO clause be "FROM" and nothing else (well, maybe "GROUP BY" and some other cases, but in any case a fully reserved word). As things stand, to support INTO-someplace-else we have to suppose that anything other than identifiers and commas is not part of INTO but belongs to the SELECT expression list. It's precisely the lack of any clear delimiter between INTO's arguments and the main SELECT syntax that is biting us, and as long as we allow INTO somewhere other than after the SELECT expression list, we can't have a delimiter because of the historical choice not to. regards, tom lane -- 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 request: fsync and commit_delay options per database
=?UTF-8?Q?Br=C3=A1ulio_Bhavamitra?= writes:
> Besides increasing it, it would be great to have these two options
> (fsync and commit_delay) per database, that is, valid only for
> databases configured with them. That would greatly speed up test
> running and still make the cluster available for other "real"
> databases.
> Is this feature or something similar planned?
No. Neither of them make any sense per-database.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
