Re: [GENERAL] Chante domain type - Postgres 9.2

2016-09-26 Thread Tom Lane
L extension." > Does that trick remove the overhead (length check) Tom mentioned upstream? Partly. It should get rid of actual calls to the varchar length checking function. There's still some distributed overhead arising from the fact that text, not varchar, is the native string type in

Re: [GENERAL] inconsistent behaviour of set-returning functions in sub-query with random()

2016-09-26 Thread Tom Lane
Tom van Tilburg writes: > I'm often using the WHERE clause random() > 0.5 to pick a random subset of > my data. Now I noticed that when using a set-returning function in a > sub-query, I either get the whole set or none (meaning that the WHERE > random() > 0.5 clause is i

Re: [GENERAL] lost synchronization with server: got message type "Z"

2016-09-27 Thread Tom Lane
most recent related bugfix I can find in the 9.2 commit history was in libpq, and it came out in 9.2.8. 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] inconsistent behaviour of set-returning functions in sub-query with random()

2016-09-27 Thread Tom Lane
Tom van Tilburg writes: > Good to know and I agree that it is not an urgent case. > I think this practice might be more common in the POSTGIS community where > there are plenty of set-returning-functions used in this way. My use was > taking a random sample of a pointcloud distru

Re: [GENERAL] Database fixed size

2016-09-28 Thread Tom Lane
out of WAL space. Hitting a limit on table size per se behaves a bit more sanely, though even there you can get into trouble --- for instance, in some situations VACUUM will try to allocate additional disk space, making recovery harder. regards, tom lane -- Sent via pgsq

Re: [GENERAL] Сreate parallel aggregate

2016-09-29 Thread Tom Lane
COPY are documented. And probably we should strip out all but the historical options from the list that we claim works with it. A more aggressive answer would be to drop the old-style CREATE AGGREGATE syntax altogether ... but seeing that we're still supporting pre-7.3 COPY syntax, probably th

Re: [GENERAL] lost synchronization with server: got message type "Z"

2016-09-29 Thread Tom Lane
et an up-to-date version of libpq and see if that doesn't make things better. Note that I never heard of "Adobe Campaign" before, and have no idea what it would take to link it against a newer libpq. You might have to get Adobe involved, unless it goes through DBD::Pg.

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

2016-09-29 Thread Tom Lane
eem to be taking the trouble to open the files in binary mode. Could that lead to the reported failure? Not sure, but it seems like at the least it could result in corrupted VM files. Has anyone tested vismap upgrades on Windows, and made an effort to validate that the output wasn't garbage?

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

2016-09-29 Thread Tom Lane
y. We should rewrite these functions to just error out internally, which will make it much easier to provide decent error reporting indicating which call failed. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

2016-09-30 Thread Tom Lane
dependent. Somebody will need to trace through this on Windows and see where it's going off the rails. 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] System crashed: fatal error restarting postgres

2016-09-30 Thread Tom Lane
why you're running *any* java application with root privileges, which is what I think would be needed to let this happen. 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] Parallel query only when EXPLAIN ANALYZEd

2016-09-30 Thread Tom Lane
David Rowley writes: > On 1 October 2016 at 05:47, Tom Lane wrote: >> Somebody will need to trace through this on Windows and see where it's >> going off the rails. > I tried the test case on 9.6.0 on a Windows 8.1 machine, and it works > fine for me. Hm, but if we a

Re: [GENERAL] System crashed: fatal error restarting postgres

2016-09-30 Thread Tom Lane
Rich Shepard writes: > On Fri, 30 Sep 2016, Tom Lane wrote: >> Wrong permissions on /dev/shm, perhaps? >Yes. I keep forgetting about this since I don't reboot this > server/workstation often. You ought to do some investigation and figure out what is causing it to co

Re: [GENERAL] pgadmin vs psql output

2016-09-30 Thread Tom Lane
1" > Output in psql > > \x4130303030303030303030303030303030303030303030303030303030303031 > is there some setting in psql output I need to take care of. See "bytea_output" parameter. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

2016-10-03 Thread Tom Lane
op that operator from the source database, as "=>" isn't a legal operator name anymore. This appears to be left over from a pre-9.0 version of hstore. 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 pg_upgrade after change of unix user running db.

2016-10-03 Thread Tom Lane
Benedikt Grundmann writes: > On 3 October 2016 at 14:12, Tom Lane wrote: >> You're going to need to manually drop that operator from the source >> database, as "=>" isn't a legal operator name anymore. This appears >> to be left over from a pre-9.

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

2016-10-03 Thread Tom Lane
E conrelid = 'js_activity_20110101'::regclass; and likewise for the parent 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] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

2016-10-03 Thread Tom Lane
you something like "16MB" in the unit column, I think. 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 pg_upgrade after change of unix user running db.

2016-10-03 Thread Tom Lane
ns out to be, it might be something we choose to fix only in HEAD. 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] BRIN indexes and ORDER BY

2016-10-05 Thread Tom Lane
Alvaro Herrera writes: > Darren Lafreniere wrote: >> We found a pgsql-hackers thread from about a year ago about optimizing >> ORDER BY for BRIN indexes. Tom Lane suggested that he was working on it: >> https://www.postgresql.org/message-id/11881.1443393360%40sss.pgh.pa.

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

2016-10-06 Thread Tom Lane
;t exist. Is there more DDL going on that you have not shown us? regards, tom lane [1] at least, since PG 9.2 or thereabouts. -- 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] Libpq functions & string to obtain connection parameters

2016-10-07 Thread Tom Lane
enabled on the server > Is it normal to still have this error ? Adding sslmode param will not > change anything as it is ignored for Unix domain socket communication. That message isn't coming out of libpq; I can't find any such string in the community git repo.

Re: [GENERAL] custom average window function failure

2016-10-09 Thread Tom Lane
the aggregate/windowfunction area. Possibly me :-(. Haven't found exactly where things are going off the rails, but it's clearly a PG bug. Thanks for the report! regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] custom average window function failure

2016-10-09 Thread Tom Lane
Adrian Klaver writes: > On 10/09/2016 08:46 AM, Tom Lane wrote: >> Clearly a bug --- the wrong type OIDs are being passed down to >> array_append. It should be told that it's getting called as > For my edification, why does this work?: On closer inspection, the error i

Re: [GENERAL] custom average window function failure

2016-10-09 Thread Tom Lane
"Sebastian P. Luque" writes: > Tom Lane wrote: >> On closer inspection, the error is only in the >> aggregate-used-as-window-function case, not plain aggregation. > Yes, I see the same phenomenon. Could someone suggest a workaround > until this is fixed? I&#

Re: [GENERAL] psql 9.3 automatic recovery in progress

2016-10-10 Thread Tom Lane
target some database process. You need to do something to reduce memory pressure and/or disable memory overcommit so that that doesn't happen. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your s

Re: [GENERAL] psql 9.3 automatic recovery in progress

2016-10-10 Thread Tom Lane
Postgres. 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: [SPAM] [GENERAL] psql 9.3 automatic recovery in progress

2016-10-10 Thread Tom Lane
en't already expecting the children to die, it would have reacted differently. 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] LOG: munmap(0x7fff80000000) failed: Invalid argument

2016-10-10 Thread Tom Lane
eems pretty fishy to me; I don't know what would be causing it. [ digs in code... ] One theory is that PGSharedMemoryDetach is getting called more than once, but I'm not sure how that would happen. Can you characterize where this happens more precisely? What nondefault settings hav

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

2016-10-10 Thread Tom Lane
Andres Freund writes: > On 2016-10-10 18:21:48 -0400, Tom Lane wrote: >> Chris Richards writes: >>> LOG: munmap(0x7fff8000) failed: Invalid argument >> [ digs in code... ] One theory is that PGSharedMemoryDetach is getting >> called more than once, but I&#x

Re: [GENERAL] Create recursive view schema.name

2016-10-11 Thread Tom Lane
implied CTE just has the base name of the view; but since CTE names can't be qualified, that's not that hard to guess. Short answer is that you don't qualify the view's internal self-reference, even if you are using a schema name in the CREATE. rega

Re: [GENERAL] confusion about user paring with pg_hba and pg_ident

2016-10-11 Thread Tom Lane
nts to log in as mailman, and likewise mattermost needs to specify mmuser. If it's not practical to make the client applications send non-default user names, you'll need to rename the Postgres roles to match the external user names. regards, tom lane -- Sent via

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

2016-10-11 Thread Tom Lane
ge size, but I've not heard that mmap() might succeed and then munmap() fail. That seems like what's happening to you 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] Passing of where clause to remote table in FDW

2016-10-13 Thread Tom Lane
execution. Use the || operator instead. 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] "The index is not optimal" GiST warnings

2016-10-13 Thread Tom Lane
aterange-first index is faster, and I'd tend to believe that over any other evidence. 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 connect to server

2016-10-15 Thread Tom Lane
ht as to why the server's not running. 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] custom average window function failure

2016-10-15 Thread Tom Lane
Sebastian Luque writes: > Tom Lane wrote: >> Are you in a position to apply patches? It's a one-line fix: >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 > I'd like to try this by obtaining the Debian

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

2016-10-18 Thread Tom Lane
; parser of our own will do? Googling found no candidates. Personally, I'd try looking in pg_depend to see if the column's default expression has a dependency on a relation of type sequence. That avoids all the fun of parsing the expression and turns it into a simple SQL join problem.

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

2016-10-18 Thread Tom Lane
Thomas Kellerer writes: > Tom Lane schrieb am 18.10.2016 um 15:20: >> Personally, I'd try looking in pg_depend to see if the column's default >> expression has a dependency on a relation of type sequence. That avoids >> all the fun of parsing the expression and tur

Re: [GENERAL] tablesample performance

2016-10-18 Thread Tom Lane
method, since the rows it grabs will be consecutive. 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] tablesample performance

2016-10-18 Thread Tom Lane
Simon Riggs writes: > On 18 October 2016 at 19:34, Tom Lane wrote: >> If you don't want to have an implicit bias towards earlier blocks, >> I don't think that either standard tablesample method is really what >> you want. >> >> The contrib/tsm_system_

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Tom Lane
://www.postgresql.org/docs/9.6/static/role-removal.html 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] configure PostgreSQL with PERL: Perl version 5.8 or later is required, but this is .

2016-10-20 Thread Tom Lane
or: Perl not found > Why does it say, "Perl version 5.8 or later is required, but this is ."? It's trying to extract the Perl version number from the output of "perl -v", and evidently failing to find one. What does "perl -v" print with that version of Perl?

Re: [GENERAL] configure PostgreSQL with the python: distutils module not found

2016-10-20 Thread Tom Lane
Python. Looking into config.log to see what the stderr output of this test was might be informative. 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] make PostgreSQL with TCLSH: No rule to make target

2016-10-20 Thread Tom Lane
maybe the bad value for TCLSH is somehow causing this, though I'm not sure how. 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 delete role because it depends on "default privileges"

2016-10-21 Thread Tom Lane
cts depend on it > DETAIL: privileges for default privileges on new types belonging to role > role_main See DROP OWNED BY. https://www.postgresql.org/docs/9.6/static/role-removal.html regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] Cannot delete role because it depends on "default privileges"

2016-10-21 Thread Tom Lane
he *current* database. You need to do DROP OWNED BY in that database (and maybe other ones, but start 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] checkpoint write errors

2016-10-21 Thread Tom Lane
EX on whatever table equates to > "base/1029860192/1029863651"? If so how do I determine the db and table > for "base/1029860192/1029863651"? 1029860192 is the OID of the database's pg_database row. 1029863651 is the relfilenode in the relation's pg_class row.

Re: [GENERAL] checkpoint write errors ( getting worse )

2016-10-22 Thread Tom Lane
it's starting to sound like that system has got major problems. You'd be well advised to focus all your efforts on getting a valid dump, not bringing it back into production. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgre

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-23 Thread Tom Lane
g about it right now. 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] Errors while installing PostGIS by an unusual method

2016-10-24 Thread Tom Lane
t PostGIS's configure even pays attention to what pg_config says for INCLUDEDIR. I've not checked.) Is there a good reason why you're not building Postgres on the same machine where you're building PostGIS? It seems like a recipe for trouble, with little to be gained.

Re: [GENERAL] What is the 'data2' directory for?

2016-10-25 Thread Tom Lane
certainly does no such thing. Maybe it's being done in some wrapper script you're using? 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] The consequenses of interrupted vacuum

2016-10-27 Thread Tom Lane
t's probably making some progress but not much. You need to fix 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] libpq heartbeat

2016-10-27 Thread Tom Lane
ent/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS 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] deadlock error - version 8.4 on CentOS 6

2016-10-28 Thread Tom Lane
information about it in the postmaster log. 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] deadlock error - version 8.4 on CentOS 6

2016-10-28 Thread Tom Lane
which is blocking whatever the "update t_unit_status_log" command wants to do with t_unit. Looks like a classic lock-strength-upgrade mistake to me. 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] WHERE ... IN condition and multiple columns in subquery

2016-10-28 Thread Tom Lane
CTE twice; but since you're evaluating that CTE elsewhere in the query, I think the additional follower node isn't worth trying to get rid of. 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] deadlock error - version 8.4 on CentOS 6

2016-10-28 Thread Tom Lane
Steve Clark writes: > On 10/28/2016 09:48 AM, Tom Lane wrote: >> Retrying might be a usable band-aid, but really this is an application >> logic error. The code that is trying to do "lock table t_unit in >> exclusive mode" must already hold some lower-level lock

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

2016-10-28 Thread Tom Lane
null. The bigger picture here is that if you've designed a data representation that requires that a null be considered "equal to" another null, you're really going to be fighting against the basic semantics of SQL. You'd be best off to rethink the representation. We've not see

Re: [GENERAL] slow performance of array_agg after upgrade from 9.2 to 9.5

2016-10-28 Thread Tom Lane
ormance, but have started a thread about it on -hackers: https://www.postgresql.org/message-id/6315.1477677885%40sss.pgh.pa.us 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] E-R diagram code U1

2016-10-29 Thread Tom Lane
x27;ve no idea what DBMS they're using (but suspect Oracle). My > web search used in-effective terms so I could not find the answer there. >What does U1 represent? Unique constraint, perhaps? I'm just guessing. regards, tom lane -- Sent via pgsql-ge

Re: [GENERAL] Row level security performance joining large tables

2016-10-29 Thread Tom Lane
he > policy check. Join cases with RLS aren't optimized very well at the moment. There's work afoot to improve this - see https://www.postgresql.org/message-id/flat/8185.1477432701%40sss.pgh.pa.us - but it won't be in production before v10. regards, t

[GENERAL] WAL segmentes names in wrong order?

2016-11-03 Thread Tom DalPozzo
Hi, I found, in pg_xlog dir, several WAL segment files with old modification timestamp but with their names greater than more recent files. Ex.: 000100C6 modified today 000100DE modified yesterday I thought it could not be possible. I'm doing some tests w

Re: [GENERAL] WAL segmentes names in wrong order?

2016-11-03 Thread Tom DalPozzo
Hi, so if I understand right, the ...DE file's previous name, was less than ...C6, then it was renamed in big advance for later use. I was missing this advance. Thanks! Pupillo 2016-11-03 11:45 GMT+01:00 hubert depesz lubaczewski : > On Thu, Nov 03, 2016 at 11:28:57AM +0100, Tom DalPoz

Re: [GENERAL] CachedPlan logs until full disk

2016-11-04 Thread Tom Lane
't think plpgsql coped with that very well before 9.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] Trouble with regexp_matches

2016-11-05 Thread Tom Lane
exp_matches(observacao, '\d\d/\d\d/\d\d\d\d')) from ... As of v10 there will be a less confusing solution: use regexp_match() instead. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

[GENERAL] checkpoint_timout with no WAL activity

2016-11-07 Thread Tom DalPozzo
Hi, I have: checkpoint_timeout = 2min wal_level = archive archive_mode=on archive_timeout = 30 With NO dbase activity, I see the WAL being modified every 2min (and, consequently, one WAL file archived every 2min too ). Is it right? I read: "If no WAL has been written since the previous checkpoint

Re: [GENERAL] checkpoint_timout with no WAL activity

2016-11-07 Thread Tom DalPozzo
2016-11-07 13:14 GMT+01:00 amul sul : > On Mon, Nov 7, 2016 at 4:20 PM, Tom DalPozzo wrote: > > Hi, > > I have: > > checkpoint_timeout = 2min > > wal_level = archive > > archive_mode=on > > archive_timeout = 30 > > > > With NO

Re: [GENERAL] checkpoint_timout with no WAL activity

2016-11-07 Thread Tom DalPozzo
I'm using 9.5.3 . I had read about that bug but I didn't know that wal_level=archive is equivalent to hot_standby from this point of view! I guess it's equivalent in 9.5.3 too. Regards Pupillo 2016-11-07 13:26 GMT+01:00 Michael Paquier : > On Mon, Nov 7, 2016 at 9:21 PM, T

Re: [GENERAL] Changing foreign key referential actions in big databases

2016-11-07 Thread Tom Lane
d probably get away with all that as long as your application isn't doing anything that makes it matter critically which semantics get applied while the changeover is being made. But test on a scratch database ... regards, tom lane -- Sent via pgsql-general mailing

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

2016-11-08 Thread Tom Lane
s. I speculate that you need to ANALYZE this table. If there are a lot of distinct possible values in the arrays, increasing the statistics target for the column might be needed. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

2016-11-08 Thread Tom Lane
any real estimation logic until 9.6. 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] ENABLE ROW LEVEL SECURITY cause huge produce of checkpoints

2016-11-09 Thread Tom Lane
It's not something we'd think of back-patching, 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] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

2016-11-10 Thread Tom Lane
r(pg_catalog.@>)"), or you could upgrade to 9.6 (don't forget to do ALTER EXTENSION ... UPDATE afterwards). 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] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

2016-11-10 Thread Tom Lane
Jeff Janes writes: > On Thu, Nov 10, 2016 at 7:11 AM, Tom Lane wrote: >> If you are using that contrib module, and it's capturing this operator >> reference, that would probably explain the bad estimate. You could >> drop the extension if you're not dependin

Re: [GENERAL] row => text => row

2016-11-11 Thread Tom Lane
with dummy as (select * from pg_class c limit 10) insert into test select * from dummy; 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] Change column type from int to bigint - quickest way

2016-11-11 Thread Tom Lane
x27;id'; That WILL break your 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] Why is this query not using GIN index?

2016-11-13 Thread Tom Lane
being significantly hurt by inadequate work_mem. 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] Converting a TimestampTz into a C# DateTime

2016-11-14 Thread Tom Lane
ient it's also directly available from PQparameterStatus(). 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_restore --clean failing due to dependancies

2016-11-15 Thread Tom Lane
t still be the case that A can't be restored before C). If you think neither of those cases apply, please provide a self-contained test case. 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_restore --clean failing due to dependancies

2016-11-15 Thread Tom Lane
:\temp.dump" > fails. [ squint... ] That does look like it ought to work. I wonder if postgis is doing something weird? But again, it's impossible to debug this at this level of detail. If you can make a test case I'd be happy to look into it. re

Re: [GENERAL] pg_restore --clean failing due to dependancies

2016-11-16 Thread Tom Lane
to not assume that the underlying tables have primary keys. It looks like in view_temp_export_geo_recherche_extra_sites_projets you need to add c.official_language_id to the GROUP BY, and similarly in view_temp_export_geo_recherche_offtrad_sites. regards, tom lane -- Sent vi

Re: [GENERAL] [HACKERS] How to change order sort of table in HashJoin

2016-11-19 Thread Tom Lane
ts of comparisons for common values. 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 migration to RDS issues permissions

2016-11-19 Thread Tom Lane
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 migration to RDS issues permissions

2016-11-19 Thread Tom Lane
uld just ignore the errors relating to plpgsql, but if you have C-language functions that you need to migrate, there is no way to install those without superuser privileges. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

Re: [GENERAL] Partial update on an postgres upsert violates constraint

2016-11-19 Thread Tom Lane
d, the given INSERT request fails due to not-null constraints that are unrelated to what the ON CONFLICT clause tests for. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgres

Re: [GENERAL] [HACKERS] How to change order sort of table in HashJoin

2016-11-20 Thread Tom Lane
random sample came up quite a bit different). And I'm a little suspicious that these tests weren't all done with the same work_mem setting. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] Strict min and max aggregate functions

2016-11-20 Thread Tom Lane
on just to see, but I doubt it's a promising avenue. 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 9.5 and Shell scripts/variables vs. C programming/defining a value to be used

2016-11-22 Thread Tom Lane
Poul Kristensen writes: > When I use this in my code I get > "undefined reference to `PQexecParms' > when compiling. IIRC, it's PQexecParams not PQexecParms regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used

2016-11-23 Thread Tom Lane
looks like Poul is hoping to use a parameter as a table name, which doesn't work. Parameters can only represent values (constants). 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] query locks up when run concurrently

2016-11-23 Thread Tom Lane
er, which leads me to wonder if the parallel calls are likely to be fighting over inserting/updating the same row in the group_history partition tables. Or are you certain that they should be hitting different rows? regards, tom lane -- Sent via pgsql-general mailing lis

Re: [GENERAL] Partial update on an postgres upsert violates constraint

2016-11-23 Thread Tom Lane
u're going to do if it doesn't, though, since you still haven't got enough data to do an INSERT. 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] Query with large in clauses uses a lot of memory

2016-11-23 Thread Tom Lane
n out-of-line parameter. Don't know how hard it might be to arm-wrestle ActiveRecord into doing it like 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] Extension compatibility between postgresql minor version

2016-11-24 Thread Tom Lane
s that cause breakage of extensions. But we try hard to avoid such changes in minor releases, particularly in long-stable branches. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postg

Re: [GENERAL] Query regarding deadlock

2016-11-24 Thread Tom Lane
2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4') > Process 4111: REINDEX TABLE table1 Offhand I would not expect those two commands to deadlock in isolation. Are they parts of larger transactions that take additional locks? regards, tom

Re: [GENERAL] pg_am access in simple transaction?

2016-11-25 Thread Tom Lane
pened and no others. There are other scenarios where file access would occur, of course, but this seems like a plausible explanation. 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] PostgreDB stores table name is lower case

2016-11-25 Thread Tom Lane
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] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Tom Lane
for an indexscan on it to be faster than a seqscan. I think however that the "half" may be a default estimate occasioned by the other tables being empty and therefore not having any statistics. Another rule of thumb is that the plans you get for tiny tables have little to do with what

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Tom Lane
Whether the OP's particular query is being hit by that is impossible to tell, though, since there isn't any actual RLS usage in the doubtless-oversimplified example. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] maintaining backwards compatibility for to_regclass argument type change from cstring to text

2016-11-30 Thread Tom Lane
on. An example: Personally, I'd try to convert everything to new style, and put a shim function into pre-9.6 deployments only. This seems to work: create function to_regclass(text) returns regclass language sql as 'select to_regclass($1::cstring)'; rega

<    1   2   3   4   5   6   7   8   9   10   >