Re: [GENERAL] Dealing with number formats when server and client are different locales

2017-09-06 Thread Tom Lane
fix your app. You could maybe make it insert to_number() calls, but it'd almost certainly be easier to get it to output numbers in SQL-standard syntax in the first place. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] Dealing with number formats when server and client are different locales

2017-09-06 Thread Tom Lane
See lc_numeric. https://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION https://www.postgresql.org/docs/current/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT regards, tom lane -- Sent via pgsql-ge

Re: [GENERAL] Accessiing database from inside of postgresql C function

2017-09-05 Thread Tom Lane
gal)? Recommended way is to use SPI: https://www.postgresql.org/docs/current/static/spi.html Aside from that documentation, there are lots of examples to study in the core code and contrib. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] Not possible to compare regrole in a view query?

2017-09-05 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] What's the best way in postgres to use ANY() with LIKE '%'?

2017-09-04 Thread Tom Lane
oesn't work syntactically. Don't recall the details offhand. 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] What's the best way in postgres to use ANY() with LIKE '%'?

2017-09-04 Thread Tom Lane
round to providing such a thing in core ... probably lack of consensus on what to name the reverse operator. You'd need to support regex cases as well, so there's more than one operator name to come up with. regards, tom lane -- Sent via pgsql-general mailing lis

Re: [GENERAL] ENUM type size

2017-09-02 Thread Tom Lane
um catalog, so you definitely need more space. Other ways of doing it would have created problems of their own. But you can certainly build your own enum type if you don't like the tradeoffs the core code made. regards, tom lane -- Sent via pgsql-general mailing l

Re: [GENERAL] Numeric numbers

2017-09-02 Thread Tom Lane
ere are an infinite number of zeroes after it seems rather beside the point. 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] Fields re-ordered on JOIN with * and USING

2017-09-01 Thread Tom Lane
TR1, TR2 Later versions of the standard use many more words to say the same thing. 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] Fields re-ordered on JOIN with * and USING

2017-09-01 Thread Tom Lane
al values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Re: [GENERAL] Strange SQL result - any ideas.

2017-09-01 Thread Tom Lane
w) As far as that UNION query goes, I think you misunderstand what UNION does. It doesn't promise to preserve ordering. You might have gotten the results you expected with UNION ALL (but they still wouldn't have constituted a valid JSON array). 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] RemoveIPC problem

2017-08-31 Thread Tom Lane
t that poses a data corruption risk. (But I've been wrong before.) 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] Problem with Lateral ?

2017-08-31 Thread Tom Lane
erusal of the 9.3-branch commit log finds quite a few LATERAL-related bug fixes that went in since 9.3.6. You might consider updating to 9.3.something-recent and see if anything changes. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.

Re: [GENERAL] Table create time

2017-08-31 Thread Tom Lane
te from SQL, whereupon it loses value for some purposes because you can never be sure that what you're looking at is the "real" date and not something somebody frobbed later. OTOH, losing all your creation date info during dump/restore is annoying too. regards, tom l

Re: [GENERAL] Out of memory/corrupted shared memory problem on server

2017-08-29 Thread Tom Lane
Johann Spies <johann.sp...@gmail.com> writes: > On 25 August 2017 at 13:48, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Remember that "work_mem" is "work memory per plan node", so a complex >> query could easily chew up a multiple of that number --

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

2017-08-28 Thread Tom Lane
creating a program that opens its own connection to the DB and sits there listening. psql cannot help you meaningfully with this request, and I can't see a way to make it do so that wouldn't be a monstrous kluge. regards, tom lane -- Sent via pgsql-general ma

Re: [GENERAL] hrs, mins and seconds do not appear with to_char

2017-08-27 Thread Tom Lane
esql.org/docs/current/static/datatype-datetime.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] Using the dollar sign as a prefix for named parameter of prepared statement.

2017-08-27 Thread Tom Lane
distinguish named parameters from dollar-quote tags? For instance, this is legal: regression=# select $foobar$stuff$foobar$; ?column? -- stuff (1 row) I think you're going to end up with weird corner case behaviors if you try to squeeze still another meaning into "$letters..."

Re: [GENERAL] Change location of function/type installed from C-extension

2017-08-26 Thread Tom Lane
Dmitry Lazurkin <dila...@gmail.com> writes: > Thanks. Can I update "pg_proc.probin" without any problems? Should work. I'd experiment in a scratch database before doing it in production, but I can't think of a problem offhand. regards, tom lane

Re: [GENERAL] Change location of function/type installed from C-extension

2017-08-26 Thread Tom Lane
pg_relation_filenode +-- pg_proc_oid_index |12662 pg_proc|12657 pg_proc_proname_args_nsp_index |12663 (3 rows) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-g

Re: [GENERAL] Retrieving query results

2017-08-26 Thread Tom Lane
Michael Paquier <michael.paqu...@gmail.com> writes: > On Fri, Aug 25, 2017 at 8:10 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> I think the real problem occurs where we realloc the array bigger. > Looking at the surroundings, I think that it would be nice to have > pq

Re: [GENERAL] Out of memory/corrupted shared memory problem on server

2017-08-25 Thread Tom Lane
ap as well as details about your query to make progress. 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] Retrieving query results

2017-08-24 Thread Tom Lane
m, certainly. 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] Retrieving query results

2017-08-24 Thread Tom Lane
Michael Paquier <michael.paqu...@gmail.com> writes: > On Thu, Aug 24, 2017 at 11:56 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> I haven't tried it, but it sure looks like it would, if you don't hit >> OOM first. pqAddTuple() isn't doing anything to guard against int

Re: [GENERAL] creating tables in tablespace

2017-08-24 Thread Tom Lane
Tiffany Thang <tiffanyth...@gmail.com> writes: > Thanks Tom. As the superuser, I'm able to create the table in the specific > tablespace. Does myuser require additional privileges? Yes, USAGE on the tablespace if memory serves (check the GRANT man page for details). > My ai

Re: [GENERAL] creating tables in tablespace

2017-08-24 Thread Tom Lane
across more than one tablespace to get anything in that column. > "show default_tablespace" is also empty. If you didn't do anything to change that setting, that would also be expected. Again, the interpretation is "use the database's default tablespace".

Re: [GENERAL] Retrieving query results

2017-08-24 Thread Tom Lane
Igor Korot <ikoro...@gmail.com> writes: > On Thu, Aug 24, 2017 at 8:51 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> I think what we need is to (1) introduce some error checking in libpq so >> that it reports an error if the resultset exceeds 2G rows --- right now >>

Re: [GENERAL] Retrieving query results

2017-08-24 Thread Tom Lane
this is explained as a library-wide limitation and not just a problem with PQntuples. 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] Explain analyse and toasted data.

2017-08-24 Thread Tom Lane
NALYZE does not do that. It likewise does not do anything to try to model the network transmission costs, which are also likely to be significant if the data is bulky --- but there's no way to do that without actually sending the result data to the client, AFAICS. regards,

Re: [GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

2017-08-24 Thread Tom Lane
d temp tables by putting the modifier word right there, rather than attaching it as an option somewhere later in the command. We should not let that syntax accident drive what we consider reasonable semantics to be. (Also, once you've done all that, do you want to also do it for UNLOGGED tab

Re: [GENERAL] 'value too long' and before insert/update trigger

2017-08-24 Thread Tom Lane
Right, that's why this isn't likely to change. You could replace the datatype-related limit with a CHECK constraint, and then it'd be possible for a BEFORE trigger to modify the value to make it compliant before the constraint is checked. regards, tom lane -- Sent via pgsq

Re: [GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

2017-08-23 Thread Tom Lane
ROP TABLE pg_temp.tablename". 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] Porting libpq to QNX 4.25

2017-08-22 Thread Tom Lane
release. It's possible it was broken for awhile before that, though, since the reason for killing it was that no one had shown any interest in testing it in a long time. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?

2017-08-16 Thread Tom Lane
gmb <gmbou...@gmail.com> writes: > Tom Lane-2 wrote >> Personally I'd have left the function parameters as text and inserted >> explicit coercions: > Just out of curiosity , is there a reason why this will be you preference ? Well, if the rest of your code thinks that

Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?

2017-08-16 Thread Tom Lane
iate type automatically. But in a function, those parameters already have types, and they might not be the most desirable ones for the purpose. Personally I'd have left the function parameters as text and inserted explicit coercions: SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname =

Re: [GENERAL] pg_column_size strange result...

2017-08-16 Thread Tom Lane
h word. 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] Help on Index only scan

2017-08-13 Thread Tom Lane
ate overhead to have such an index. In any case, I wouldn't worry about it until you have an actual performance problem. Trying to tell on toy data what the planner will do with production-sized data is usually a losing game. regards, tom lane -- Sent via pgsql-gen

Re: [GENERAL] Fwd: 2 process postgres -D for one instance

2017-08-11 Thread Tom Lane
0 squares with 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] Curious planning decision

2017-08-10 Thread Tom Lane
rprising if you're using the default statistics target of 100 --- that means that the accuracy of histogram-related predictions can't be expected to be any better than 1%. If you crank up the stats target (for this column, or the whole table, or globally) and re-analyze, the estimate should get better,

Re: [GENERAL] foreign key with char and varchar

2017-08-10 Thread Tom Lane
one PK row that "matches" an FK row. If this is explained anywhere in the user-facing documentation, I didn't find it in a quick look :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Isolation of schema renames

2017-08-09 Thread Tom Lane
ALTERs would succeed, and then S2 has no choice but to respect the results of that DDL. There are other ways we could define the results of this sort of thing, but they generally would end up failing one or both of your transactions. Letting it "just work" is not in the picture.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Tom Lane
extension. If the answer is "nothing" then it wouldn't be hard to add such a statement. 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] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Tom Lane
Melvin Davidson <melvin6...@gmail.com> writes: > *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE extowner = > {oid_from_above_statement};* Note you'll also have to modify the rows in pg_shdepend that reflect this ownership property. regar

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

2017-08-09 Thread Tom Lane
uthoritative list of the symbols meant to be exported from core Postgres to extensions, you could use that to filter out unnecessary symbols. Unfortunately, no such list has ever been prepared :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

2017-08-08 Thread Tom Lane
r...@bb-c.de (Rainer J.H. Brandt) writes: > Tom Lane writes: >> I bet not. We've seen problems with macOS unexpectedly deciding to >> filter away inherited environment variables in some situations. >> It might be useful to put "env >somefile" into the PG makef

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

2017-08-08 Thread Tom Lane
ent variables in some situations. It might be useful to put "env >somefile" into the PG makefile and compare results between the two ways of invoking it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Lifetime of PQexecPrepared() returned value

2017-08-05 Thread Tom Lane
ing, it's not unreasonable to write PQclear(PQexec(conn, "some SQL command")); 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] Lifetime of PQexecPrepared() returned value

2017-08-04 Thread Tom Lane
oyed, which this coding suggests would happen, then that shouldn't matter ... but maybe the pointers got copied to somewhere longer-lived? Anyway, there's nothing visibly wrong with what you showed us, so the problem is somewhere else. regards, tom lane -- Sent via pgsql-g

Re: [GENERAL] Check if prepared statement exist?

2017-08-03 Thread Tom Lane
Igor Korot <ikoro...@gmail.com> writes: > Is there a way to do such a check from the libpq? I think the pg_prepared_statements view will help you. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] Fwd: Planner oversight for GIN indices?

2017-08-02 Thread Tom Lane
ause we'd definitely have the actual comparison value at runtime.) Anyway, sorry, this is a research problem rather than something that's easy to fix. 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 you add a --dry-run to pg_restore?

2017-08-02 Thread Tom Lane
chive is corrupt enough that it contains bad SQL, it probably has problems that pg_restore would notice anyway. Most of the restore failures that we hear about in practice would not be detectable without actually executing the commands, because they involve problems like issuing commands in the wrong orde

Re: [HACKERS] [GENERAL] Not able to create collation on Windows

2017-08-01 Thread Tom Lane
ATION ... FROM "C" at all on platforms that lack HAVE_LOCALE_T. There's no good reason for that IMO; not if we're one line of code away from allowing 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: [HACKERS] [GENERAL] Not able to create collation on Windows

2017-08-01 Thread Tom Lane
Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes: > On 8/1/17 10:53, Tom Lane wrote: >> I think this is actually a bug, because the collations code clearly >> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c, > You seem to say that we

Re: [GENERAL] Not able to create collation on Windows

2017-08-01 Thread Tom Lane
ng silly errors, just hot-wire the code to assume ENOENT on Windows. 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] Partitioned TEMP tables

2017-07-31 Thread Tom Lane
; 1) -> Seq Scan on c2 (cost=0.00..25.88 rows=423 width=36) Filter: (f1 > 1) (5 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] Partitioned TEMP tables

2017-07-31 Thread Tom Lane
ated 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] Executing regex in C code

2017-07-30 Thread Tom Lane
oll() if you're trying to invoke a collation-aware function. It's probably good enough to pass DEFAULT_COLLATION_OID, although if you're inside a SQL function of your own, passing down whatever collation was passed to you would be a better plan. regards, tom lane --

Re: [GENERAL] number of referencing and referenced columns for foreign key disagree

2017-07-29 Thread Tom Lane
o use that if (sid, social) were not the PK of words_social but just some random unique key. 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] tzdata version

2017-07-28 Thread Tom Lane
don't have a provision to reload a timezone definition once read by a given process. Whether you use --with-system-tzdata doesn't matter; the same would apply if you were updating a Postgres-private copy of the tzdata files. regards, tom lane -- Sent via pgsql-gener

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-28 Thread Tom Lane
Vincenzo Romano <vincenzo.rom...@notorand.it> writes: > I would like to understand the typo protection mentioned by Tom earlier: > I need to understand the reason for creating that special case. Well, case A: create function foo(out x int4) returns setof int8 ... This is indubi

Re: [GENERAL] tzdata version

2017-07-28 Thread Tom Lane
r in another way) ? I don't know of any version labeling in the tzdata files themselves (not that I've looked very hard for one). If you know your PG minor release you could look into our release notes to see what tzdata release it shipped with. regards, tom lane -- Se

Re: [GENERAL] Why am I getting doubles?

2017-07-27 Thread Tom Lane
| pp_pkey | db| public | pp | f2 |2 | Your WHERE clause can't tell the difference between these. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-27 Thread Tom Lane
from simple typos. If you want a consistent syntax I'd suggest CREATE OR REPLACE FUNCTION afun1() RETURNS TABLE (ot text) ... It's still really "setof text" under the hood. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

2017-07-24 Thread Tom Lane
"David G. Johnston" <david.g.johns...@gmail.com> writes: > On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> The cost to form the inner hash is basically negligible whether it's >> de-duped or not, but if it's not (known) de-duped then

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

2017-07-24 Thread Tom Lane
lly negligible whether it's de-duped or not, but if it's not (known) de-duped then the cost estimate for the semijoin is going to rise some, and that discourages selecting it. At least in this example, the actual runtimes are basically identical regardless, so there is no great point in sweating over 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] pg_dump not dropping event trigger

2017-07-23 Thread Tom Lane
Greg Atkins <void.is.mean...@gmail.com> writes: > would you like a bug report to track this? No, it's already dealt with. In any case, your original email was good enough --- we track bugs these days more by message-ID than anything else. regards, tom lane --

Re: [GENERAL] pg_dump not dropping event trigger

2017-07-22 Thread Tom Lane
yes, it seems you don't get a DROP EVENT TRIGGER even with that. Will fix. 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] Backward compatibility

2017-07-22 Thread Tom Lane
' ' in version() ) ; position -- 11 (1 row) Although possibly what you really want is split_part(). regression=# select split_part(version(), ' ', 2); split_part 9.5.7 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] Bug in postgres 9.6.2?

2017-07-21 Thread Tom Lane
greigwise <greigw...@comcast.net> writes: > If I can provide a pg_dump backup with a db where I can reproduce the error > and then also my postgresql.conf along with the query, would that be what > you need for a test case? Sounds like enough. regards, tom

Re: [GENERAL] pg_dump and insert json with copy

2017-07-21 Thread Tom Lane
more likely to be a failure cascading from something else. In any case, you haven't provided nearly enough information for anyone else to investigate this problem. Please see https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane -- Sent vi

Re: [GENERAL] pg_restore misuse or bug?

2017-07-21 Thread Tom Lane
on why the superuser would get a "permission denied" > error. Matview queries are run as the owner of the matview, so this isn't as surprising as all that. But if the matview works in your normal usage, then pg_dump must be doing something wrong, perhaps emitting grants in the wrong o

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Tom Lane
John R Pierce <pie...@hogranch.com> writes: > On 7/20/2017 8:40 PM, Tom Lane wrote: >> Hm, we need to update that text for the new 2-part version numbering >> scheme, don't we? > will 10 return like 100100 if its 10.1, or 11 ? The latter. The two middle digits w

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Tom Lane
ero is returned > if the connection is bad. Hm, we need to update that text for the new 2-part version numbering scheme, don't we? 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] Bug in postgres 9.6.2?

2017-07-20 Thread Tom Lane
Can you extract a test case you wouldn't mind publishing? 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] Bug in postgres 9.6.2?

2017-07-20 Thread Tom Lane
a nondeterministic query result deterministic. 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] _page_cost parameter with values < 1

2017-07-20 Thread Tom Lane
st| 1 (7 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] How to stop array_to_json from interpolating column names that weren't there

2017-07-20 Thread Tom Lane
f a SELECT list. SQL is not the most consistent language in the world to begin with, and some of these notations are things we inherited from Berkeley PostQUEL and didn't want to give up, so it's a bit of a mess :-( regards, tom lane -- Sent via pgsql-general mailing li

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

2017-07-19 Thread Tom Lane
you're missing parentheses around the scalar sub-select. (Whether fixing that will give the behavior you want is unclear, but the syntax error is clear.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscr

Re: [GENERAL] Support for \u0000?

2017-07-19 Thread Tom Lane
PIs would affect *all* datatypes. 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] UPDATE column without FK fires other FK triggers constraint check

2017-07-19 Thread Tom Lane
oring more data on-disk than is there now. Whether that would be a good tradeoff is dubious. 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 would log_lock_waits affect a query plan?

2017-07-19 Thread Tom Lane
t's just not something I'd ever expect to affect a query plan. TBH, I don't believe it. There are a lot of moving parts here, but I don't see how that could be relevant. 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, trading a DOMAIN for its base datatype

2017-07-19 Thread Tom Lane
C GG <cgg0...@gmail.com> writes: > ... Is PostgreSQL smart enough to not have to rewrite the table and simply > shed the domain for the underlying datatype? Yes, in recent versions ... don't remember how far back exactly. regards, tom lane -- Sent via p

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

2017-07-19 Thread Tom Lane
UPDATE; COMMIT. 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 \u0000?

2017-07-19 Thread Tom Lane
legal member of strings would break all those internal APIs, requiring touching far more code than anyone would want to do. It'd likely break a great deal of client-side code as well. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Planner statistics usage for composite type

2017-07-19 Thread Tom Lane
Stephen Frost <sfr...@snowman.net> writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Yeah, that's because eval_const_expressions doesn't know how to fold >> a constant RowExpr to a simple Const. I have a patch laying about >> someplace to improve that, but I kee

Re: [GENERAL] Planner statistics usage for composite type

2017-07-19 Thread Tom Lane
tag_sim <= '(tag1,1)'::tag_sim)) Planning time: 0.230 ms Execution time: 0.110 ms (4 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] hash join performance question

2017-07-18 Thread Tom Lane
ost of a nestloop-with-inner-indexscan enough to make the planner choose that 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] spi/timetravel: unique constraint violation on UPDATE

2017-07-15 Thread Tom Lane
postgre...@get-experience.com writes: > Den 15. juli 2017 23:15, skrev Tom Lane: >> Perhaps you could make your PK be on (id, valid_from, valid_to). > Doesn't really work because valid_to would change on UPDATE. I'd need to > update foreign relations with another trigger which wou

Re: [GENERAL] spi/timetravel: unique constraint violation on UPDATE

2017-07-15 Thread Tom Lane
E UPDATE trigger, it would take a lot of magic for things to happen that way ;-). Perhaps you could make your PK be on (id, valid_from, valid_to). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

Re: [GENERAL] puzzled by deletion performance

2017-07-14 Thread Tom Lane
, although EXPLAIN ANALYZE will show a lot of time spent in the FK enforcement trigger. 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] Regarding creating pgpointcloud extension.

2017-07-13 Thread Tom Lane
t should be regular SQL, and you can see what's failing. 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: Systemd support (was:Re: [GENERAL] Please say it isn't so)

2017-07-13 Thread Tom Lane
ntly more lines of documentation concerning systemd than we do code. 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] Changing collate & ctype for an existing database

2017-07-12 Thread Tom Lane
r is more case-sensitive, for instance. 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] Changing collate & ctype for an existing database

2017-07-12 Thread Tom Lane
he wrong subtree), and by the same token insertions may fail to enforce uniqueness. That's pretty corrupt in my book. 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] loading file with en dash character into postgres 9.6.1 database

2017-07-11 Thread Tom Lane
encoding setting, so one way or another you're going to need to switch the terminal program's character set setting. 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] Does a row lock taken out in a CTE stay in place?

2017-07-11 Thread Tom Lane
CTEs are discarded. I thought maybe there was an exception for FOR UPDATE, but a look at the code says differently. In any case we would only lock rows the sub-select had actually read, so if it's not called by the outer statement it would still be a no-op. regards, tom lane

Re: [GENERAL] Changing collate & ctype for an existing database

2017-07-10 Thread Tom Lane
rihad <ri...@mail.ru> writes: > On 07/10/2017 11:07 PM, Tom Lane wrote: >> ... which that isn't. I'd suggest checking for indexes that might need >> to be rebuilt with this query borrowed from the regression tests: > I ran the query on our production database. Zero res

Re: [GENERAL] Changing collate & ctype for an existing database

2017-07-10 Thread Tom Lane
rihad <ri...@mail.ru> writes: > On 07/10/2017 08:42 PM, Tom Lane wrote: >> No, your indexes on text/char/varchar columns will be corrupted >> (because their sort order will now be wrong). If you can reindex >> them before doing anything more with the database, you'd be

Re: [GENERAL] Changing collate & ctype for an existing database

2017-07-10 Thread Tom Lane
ecause their sort order will now be wrong). If you can reindex them before doing anything more with the database, you'd be ok ... I think. Testing on a scratch copy of the database would be a good idea, if this is valuable data. regards, tom lane -- Sent via

Re: [GENERAL] psql doesn't pass on exported shell environment functions

2017-07-07 Thread Tom Lane
he exported-functions feature. 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

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