lt-in functions is as though the bootstrap
superuser has granted execute access to PUBLIC. You would need to
reverse that (and then grant it back to any non-superusers who
should have it).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql
But I really
really doubt that TCP vs unix socket is the determining factor.
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
though that the OP is guessing
about what's happening inside application-driven sessions, where it would
be hard to do that kind of debugging :-(
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
ead behavior will
currently happen at next command receipt, even if you are inside an
aborted transaction. We cannot read the system catalogs if the current
transaction is aborted, so the timing would have to be subtly different
in any case.
regards, tom lane
--
Sent
in with.
But at any rate, bottom line is that your problem is client-side not
server-side, and no amount of fooling with the function innards will
change 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
cs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
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
;s configuration.
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
future software update overwrites the zone
files.
The best compromise might be to just use <+>+0, ie force it to
print in GMT always.
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
ill work with a numeric data column. You might
lose some precision in the conversion 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
le idea of
how much space is in use or not in the 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
tion needed
for the second update to be well-defined. That statement is independent
of any particular implementation approach. There are probably ways around
that, such as not allowing the FK-involved columns to be auto updatable,
but it's really looking like a mess.
re
Willy-Bas Loos writes:
> Is there a reason for this change of behavior between 8.4 and 9.* ?
See the "incompatibilities" section in the 9.2 release notes:
* Make EXTRACT(EPOCH FROM timestamp without time zone) measure the
epoch from local midnight, not UTC midni
never did figure out what was producing that setting on
Cloos' machine. But it's not relevant to the specific problem being
complained of here.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subsc
gression=# CREATE UNIQUE INDEX idx_load_pick ON pick ((case picked when
picked='y' then load_id else null end ));
ERROR: operator does not exist: text = boolean
regression=# CREATE UNIQUE INDEX idx_load_pick ON pick ((case when picked='y'
then load_id else null end ));
C
() property. "doy" div 7 might
help, for example.
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
already possible at the C-code level, using
session_preload_libraries. It wouldn't be hard to write an extension
that exposed that in some useful way to SQL code.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
Pavel Stehule writes:
> 2015-09-26 19:59 GMT+02:00 Tom Lane :
>> A session-start hook is already possible at the C-code level, using
>> session_preload_libraries. It wouldn't be hard to write an extension
>> that exposed that in some useful way to SQL code.
> years
is
for get updated.
As an example, even if we stopped this error from occurring, there would
be no guarantee that a restore from pg_dump would populate the index
usefully, since pg_dump could have no idea that the other two tables need
to be populated before building this index.
ecause of a syntax
problem, but I do not see how a clause involving USING could have got
into the CREATE SEQUENCE command.
Could you try extracting plain-text output from the dump file, ie
pg_restore -Cv ./census.backup >census.txt
and then having a look at what's in the output file in and
reSQL,
> at least not yet.
Seems like it would be a good idea if BDR's pg_dump were to suppress
"USING local" clauses, and only output USING if it's not default, so as
not to create gratuitous incompatibilities like this one.
regards, tom lane
--
the required subtraction operator
does already exist, and you just need a wrapper function to cast the
result to float8, probably with extract(epoch ...).
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
the tablespace mechanism, per se, doesn't help you in this. It's not
designed to be a robustness aid.
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
s that an ORDER
BY expression be one of the ones being DISTINCT'd on.
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
Paolo De Michele writes:
> 2015-10-01 21:40:20 UTC FATAL: could not remove old lock file
> "postmaster.pid": Permission denied
Looks like something removed the postmaster's write permission on
the data directory itself.
regards, tom lane
--
S
s that you can do this with dblink, but *not* with FDWs
--- or at least, not with postgres_fdw. The latter is smart enough to
roll back your remote transaction when the local one rolls back.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@pos
plausible alternative is a FOR IN SELECT loop, which would
have the benefit that you could actually do something with the row values.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://w
lution,
but makes it impossible to fix manually.
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
a role that has privileges
of (is a member of) both the source and target roles. Superusers are
considered members of all roles, so that's how come it works for them.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
the REASSIGN OWNED man page.
I think it needs to be explained more prominently. Will see about making
that 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
you running? I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.
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
nk about doing a minor-version upgrade. We don't put out bug fix
releases just for idle amusement.
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
ollations? Or are their collations graven on stone tablets, unlike
anyone else's?
We certainly could stand to put some work into the problem of coping with
collation changes. But claiming that ICU is the solution, or even a
solution, seems obviously wrong.
regards,
that that would probably make some operations
slower. I don't entirely understand that objection, since (a) some other
operations would probably get faster, and (b) performance does not trump
correctness. But that's where the discussion stands at the moment.
reg
(select 1 from unaggregated ua where a.lognum=ua.lognum and flightnum
> like '8%')
That would work too, but not sure about performance relative to the other
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
T DISTINCT FROM ROW(...) test
that lists locktype and all the other lock-target-defining fields is the
best way to write 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
of its righthand input. This will happen whenever there are
duplicate keys in the lefthand input.
I think the planner does take the possibility of rescans into account
in its cost estimates, but perhaps it's not weighing it heavily
enough. It would be interesting to see what you get as a s
x27;ll get whatever version is invoked by "python", which is
most likely python2). See the build instructions in the documentation.
Also watch the output from configure, which will show you which python
it selected.
regards, tom lane
--
Sent via pgsql-general mai
n3u";
Hmm, what files *do* you have in that directory?
It might be worth cd'ing into the src/pl/plpython subdirectory and
manually doing "make install" there to see what it prints.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@
exist.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path
to include that, or else schema-qualify the function names.
regards, tom lane
--
Sent via pgsql-general maili
Probably should
establish which of those it is before going further.
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
s.
I concur with Adrian's nearby suggestion of checking for rows with
reltablespace matching the tablespace's OID before you do anything
drastic, 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
't get updated if you change the symlink. That's
okay, more or less, because it's never actually used for anything.
We got rid of that column in later versions.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To m
x27;re not accounting for the fact that such an offset wouldn't be
in the first segment file of the 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
bricklen writes:
> On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane wrote:
>> I'm confused by the block mentioned in the error message not having
>> anything to do with the TID sequence. I wonder whether it refers to an
>> index not the table proper. What query were yo
xtent these arguments could also be made to apply to
cstring_to_text_with_len, of course, but I consider that to be a sibling
of cstring_to_text, which does have considerable usefulness.)
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
more than
one step on the way to disaster.
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
add them... the biggest issue would
> probably be changing the buffer management code so it didn't assume that
> a temporary relation went into temporary buffers.
Uh, why would you do that? You'd be throwing away one of the principal
performance advantages of temp tables.
Jim Nasby writes:
> On 10/22/15 12:36 PM, Tom Lane wrote:
>> Uh, why would you do that? You'd be throwing away one of the principal
>> performance advantages of temp tables.
> Actually, it depends on what behavior you'd expect from a temporary
> index. If it
nnect.
Are those apps trying to use TCP connections, or Unix-socket connections?
If the latter, it might be a discrepancy in where they expect the socket
file to be versus where the postmaster thinks it should be.
regards, tom lane
--
Sent via pgsql-general maili
necessary to get into
such a state ... but you will need to manually remove the dup rows before
rebuilding the unique index will succeed.
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
ilities open up --- but AFAICS
we've not touched the PAM code since 8.4.2.
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
curity hazard too.
It's a good policy IMO (though I used to work there so no doubt I've just
drunk too much Red Hat koolaid).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://
008 or so, so it seems rather unlikely that anybody would have
tzdata old enough for that to be a problem.
I'm betting the OP simply didn't have Postgres' timezone parameter set
properly. Yes, that can be fixed with a reload (as a moment's
experimentation would have shown).
@postgresql.org
> <mailto:pgsql-general-ow...@postgresql.org>
> [mailto:pgsql-general-ow...@postgresql.org
> <mailto:pgsql-general-ow...@postgresql.org>] On Behalf Of Tom Dearman
> Sent: Wednesday, October 28, 2015 11:44 AM
> To: pgsql-general@postgresql.org <mailto:pgsq
pear inside a string literal,
double-quoted identifier, or $$ literal.
I'm not at all sure that it's possible to handle this requirement 100%
correctly with regexes; they're unable to do context-sensitive processing.
But so far as pg_stat_statements is concerned, why would you need to
d
e trim() checks for
remote execution when there is no suitable function on the remote side.
Don't know whether that's a bug in mysql_fdw, or whether there's some
setup you're supposed to perform on the mysql server and have omitted.
regards, tom lane
--
ng specific large tables using
per-table vacuum settings, and not sweat the small stuff at a per-DB
level.
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
quot;autovacuum" actually controls whether the
launcher is started at all, and so it cannot be set on a per-database
basis.
I believe it might work to adjust many of the other autovac parameters
at the per-database level, but not that one.
regards, tom lane
--
Sent v
Eric Schwarzenbach writes:
> ... (Also FWIW, the latest version of
> this regexp is now '^([0-9]+.)*[0-9]+$')
Um, that's not gonna do what you want at all. Outside brackets, a dot
is a wildcard. (Regex syntax is a mess :-(.)
regards, tom lane
ade, but hopefully by then Red Hat will have addressed your bug.
3. This was a mistake and you'd rather stay in en_US all round:
you can probably change the system-wide language setting somewhere,
but I do not remember where right at the moment.
regards, tom lane
;s CR/NL translation corrupts the data.
If that's true, though, the resulting backup file should be corrupt;
is it valid according to "pg_restore backupfile"?
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
ppen every 5 minutes and takes about
4.5 mins which corresponds to the 0.9 checkpoint_completion_target we have set.
> On 28 Oct 2015, at 19:20, Jeff Janes wrote:
>
> On Wed, Oct 28, 2015 at 8:43 AM, Tom Dearman wrote:
>> We have a performance problem when our postgres is under high
ing
the table's rowtype while this transaction runs.
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
permissive]
>for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)
> relpath.c:55:51: error: no 'operator++(int)' declared for postfix '++'
> [-fpermissive]
>for (forkNum = 1; forkNum <= MAX_FORKNUM; forkNum++)
The second of these definitely comes from try
"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" writes:
> El 04/11/15 a las 00:05, Tom Lane escribió:
>> Why is it invoking g++ and not gcc?
> I don't know. I just installed MinGW, then ./configure, maybe there's an
> error in the configure script?.
I looked at the con
serts.
With the numbers you're showing, auto-analyze should trigger once the
table gets to 20% new tuples. It would be interesting to see the
pg_stat_all_tables values for one of your problematic tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgs
"ALTER TABLE name DROP CONSTRAINT name".
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
LSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) +
> '1 day'::INTERVAL
Um, don't you just want lead() or lag()?
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
k into it you'll likely
find that it doesn't look anything like PG configuration data.
As already noted, postgresql.auto.conf is not for hand-editing.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
+---
f1 | integer | not null
f2 | integer |
f3 | integer |
Indexes:
"foo_pkey" PRIMARY KEY, btree (f1)
"foo_f2_key" UNIQUE CONSTRAINT, btree (f2)
"foo_f3_idx" btree (f3)
There's some additional rules for abbreviating ver
l
thing would be to prevent users from breaking their database in the
first place --- but there's not much we can do in that direction
beyond setting the directory permissions.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgre
concerns associated with table-extension behavior. Most users
would not thank us for making table extension slower in order to issue a
more intelligible error for examples like this one.)
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql
for a general-purpose connection
pooler; the first two in particular would be unacceptable 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
the point. If it is possible, how
do you keep that from being a security hole, ie one of the pool users can
gain privileges of another one?
(And, btw, I repeat that all of this has been discussed before on our
lists.)
regards, tom lane
--
Sent via pgsql-general mailin
hile keeping it away from credentials that belong to other roles?
This is certainly something that'd be useful to have, but it's not
clear how to do it in a secure fashion.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
other user.
Doesn't seem like this'd actually provide any useful functionality for a
connection pooler. It still has to restrict any one underlying connection
to be used by only one role. You've added more bookkeeping (because
there's a state where a connection's role is una
t in the first place (although I was not around at
> the time that was done--maybe there were other considerations).
I think that was just bad design. There's a lot of old stuff in contrib
that hasn't been vetted all that closely.
regards, tom lane
--
Se
eems to me that the old-style and new-style operators could coexist
just fine; neither one ought to be a large increment of unsharable code.
(Granted, it might take some refactoring to make that so.) So I think
forking would be a bad approach.
regards, tom lane
--
Sent v
cuum, likely. The rate would depend on your autovacuum_naptime
and how many active databases you have.
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
;consistent" function)
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
upport functions (mostly, the "consistent" function)
> about it.
BTW, you'd probably find this patch instructive:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f576b17cd6ba653bdace1f0da9a3b57f4984e460
although it's doing more than just adding one o
I
> don't know if it is a good idea to use that overloading.
I would vote for overloading; there's no risk of confusion that I can see.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
always advancing in lockstep. It doesn't seem like something to rely on
though; somebody might decide to move that out of the buffer critical
section to improve concurrency. In any case, neither txid_current nor the
sequence value will provide any reliable guide to the apparent commit
orde
d be in for some pain with very wide queries.
If someone were to throw lots of effort at the problem, and not care
about preserving on-disk database compatibility, no doubt all these
things could be dealt with. But I don't see it getting to the top of
the community's TODO list.
to ANALYZE, or were
using a larger statistics target in the 9.4 installation?
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
er
row insertion due to not having to apply the roundoff function. Adding
one would certainly not improve speed.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
equested the message
(VACUUM VERBOSE, for instance). Arguably, suppressing such a message
would break things, so you can't.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgr
the window wouldn't even be
that small: pg_dump's attempt to lock some previous table might've blocked
for awhile due to DDL on that 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
nknown) result as false not true.
Certainly there are things to quibble with in that behavior, but
it's what's been required by the SQL standard since 1992.
> but this is working with other databases
Really? None that are compliant with the SQL standard, for sure.
ackend functions it calls can tell, and they throw errors.
There are various ways this might be rejiggered, but none of them
entirely remove all risk of failure in the presence of concurrent DDL.
Personally I'd recommend just retrying the pg_dump until it succeeds.
rega
n, for quite a long time. AFAICS this
error would only be possible if plruby had been compiled against postgres
header files from 9.0 or before, which would be a packaging mistake.
Suggest complaining to whoever the package builder is.
regards, tom lane
--
Sent via pgsq
onsiderations
mean that the odd byte would just be wasted in most or all of the
catalogs.
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
he algorithm.
> I do not know the exact syntax.
You would need to write a plpgsql function in order to have a loop like
that; there's no loops in bare SQL.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make ch
y enforcement trigger). So any other transaction that is
accessing any two of those tables in a different order than this does
creates a deadlock hazard.
> Have you looked at separating the FK creation and validation?:
I think it'd likely be enough to add the FKs one at a time, rather
than a
is a bad guess about the size of the recursive
union result, which is unsurprising since it is only a guess. If you've
heard of ways to estimate recursive union sizes more plausibly, maybe we
could do something about that.
regards, tom lane
--
Sent via pgsql-gener
in which you might store values that aren't
canonical.
I have some recollection that we discussed this when range types
were being invented, and didn't think of any nice solution.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postg
or libc routines.
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
ith "psql -n", 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
Alvaro Herrera writes:
> Tom Lane wrote:
>> You might have better luck with "psql -n", or maybe not.
> I've wished sometimes for a "\set READLINE off" psql metacommand for
> this kind of thing. It's pretty annoying when the text being pasted
>
dline uses to do completion.
> Doesn't 'cat | psql ' disable it?
Sure, but you could as well use 'psql -n'. I think the point is to be
able to turn it on and off without starting a fresh session. (Admittedly,
maybe there's not a lot of usability gain there.)
201 - 300 of 14353 matches
Mail list logo
