P keepalive settings more aggressive.
regards, tom lane
Alexander Farber writes:
> why does not PostgreSQL 10.11 please like the -
I think you are confusing plpgsql syntax with sql syntax.
regards, tom lane
o install these before doing the upgrade, correct?
Yes, but in this context, "install" only means "add the files to the
filesystem". You don't need to do CREATE EXTENSION in the new
database; pg_upgrade will handle that when it transfers the data.
regards, tom lane
at stopping
at 9.6 is probably not your best choice for a migration today.
regards, tom lane
n. There's no point
in issuing an explicit CLOSE (and indeed, if you try, it'll just
produce the same "current transaction is aborted" error).
regards, tom lane
st-modified? etc etc). You can search the
project's mailing list archives if you want to read the prior discussions.
regards, tom lane
itdiff&h=3ca930fc3
regards, tom lane
e one you asked for.
(I haven't actually tried this, but that's how I read the docs.)
regards, tom lane
il it tries to read or write on the connection.
If it were free to improve this, we might do so. But it would be
very much not free.
regards, tom lane
in that case, though. Seems like
something you should discuss with the pgpool people.
regards, tom lane
ut if it's not
a lot of code then maybe. It certainly seems a bit inconsistent.
regards, tom lane
e to libpq in the docs
probably should be taken out, because initdb never uses libpq to make
a connection, so libpq's defaults are not relevant.
regards, tom lane
27;s not necessarily a reason not to use them, but you have to
be careful what you assume about them.
regards, tom lane
e
collation issue, though, for most people.
regards, tom lane
ere's no reason not to use it.
It's actually all the *other* collations where you should worry about
their behavior being a moving target :-(.
regards, tom lane
d is much the most reliable way to fix this.
(Or you could use serializable mode, but that feels like using a hammer
to swat a fly.)
regards, tom lane
dified
a large object.
> My gut says no as
> moving/copying potentially 4 TB of data would kill any IO.
Well, it's done on a per-chunk basis (normally about 2K per chunk),
so you won't do that much I/O unless you're changing all of a 4TB
object.
regards, tom lane
there's a chunk boundary between.
On the whole, though, it's not clear to me why concurrent updates of
sections of large objects is a good application design. You probably
ought to rethink how you're storing your data.
regards, tom lane
has been out of support for a year.
I rather imagine that the libpgrouting people wouldn't want to provide
bug fixes for that branch either :-(
regards, tom lane
levant code is
mostly in heap_lock_tuple().
regards, tom lane
prising for each child table to get
locked. (I'm not sure where *two* locks would come from, though.)
If you're working with massively partitioned tables, increasing
max_locks_per_transaction is a good idea.
regards, tom lane
ability to do client-side
query tracing. I'm not familiar with the details of that though.
regards, tom lane
n't
been high priority. If you feel like working on it, you could look
at commits e5dc4cc24d2e1e94ac572a2c64103710bf15d21e (for plperl)
and/or a5036ca998a6058f60913d43a80badfcbb65f5bb (for pltcl).
regards, tom lane
tter whether
the engine deems the RE-as-a-whole to be greedy or not.
I think this would work without needing any explicit greediness
marking for the second and third parts, but I might be wrong
about that detail.
regards, tom lane
break which of the original alternatives must've matched.
It's sort of annoying that we don't have a simple "regexp_location"
function that would give you back the starting position of the
first match.
regards, tom lane
"Daniel Verite" writes:
> FWIW, in plperl, there's a simple solution:
> $string =~ s/(foobar|foo|...)/$replace{$1}/g
Well, our manual does suggest using plperl (or pltcl) when the
built-in pattern match functions aren't adequate ;-)
regards, tom lane
e to put the WITH. That's just
part of the SQL statement as far as plpgsql is concerned, so what
you need to do to use a WITH clause in a variable assignment is
to write
variable := WITH ... SELECT ... ;
(At least, I think that will work; what you're doing definitely
won't.)
;,'chr(34)'),'''','chr(39)')
> It works but I would like to call just one Replace.
Use translate(), perhaps?
https://www.postgresql.org/docs/current/functions-string.html
regards, tom lane
u restore. I guess this fits with the definition of the switch
as selecting objects *in* the named schema, but it's still a
possible gotcha.)
regards, tom lane
u weren't confused yet: all of this depends on the particular
PL that you're considering. Of the PLs included with core PG, I think
only SQL-language functions can do the above --- the other ones are
strictly execute-to-completion. I don't know offhand about third-party
PLs.
regards, tom lane
ttps://commitfest.postgresql.org/26/2372/
However, that still is only half of the problem, because you also need
a PL that is prepared to cooperate, which I don't believe plpgsql is.
I think (might be wrong) that a plpython function using "yield" can
be made to compute its results lazily.
regards, tom lane
the function call as an
output column. It wouldn't be perfectly transparent of course; you'd
have to modify the local query to refer to that column rather than
calling the function explicitly.
regards, tom lane
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> There is, AFAIK, no provision for that. There's not a lot of
>> reason to consider adding it either, because there's no reason
>> to suppose that the remote node can run such a function any
>>
you'd better raise this with the author(s) of the "pg"
Ruby gem. Perhaps they read this mailing list, but more likely
they have a specific bug reporting mechanism somewhere.
regards, tom lane
executable on
the remote side, and that fails if you try to execute it locally, then
it'd make sense to implement that feature and then expect postgres_fdw to
try hard to push down such calls. But we don't have that (yet). I'm
unconvinced that ordinary functions ought to be treated as if remote
execution is preferable.
regards, tom lane
s checks happen. I think
you'd be all right with a traditional-style PG index, but maybe
not with a deferrable unique constraint.
regards, tom lane
, so there must be something unique to your configuration. Busted
GSSAPI library, or some ABI inconsistency, perhaps? What platform are you
on, and how did you build or obtain this Postgres code?
regards, tom lane
Kyotaro Horiguchi writes:
> At Thu, 09 Jan 2020 14:12:25 -0500, Tom Lane wrote in
>> If SQL/MED has a notion of a "foreign function" that is only executable on
>> the remote side, and that fails if you try to execute it locally, then
>> it'd make sense to im
e doesn't get a lot of updates then creating an expression
index on that expression might be useful.
regards, tom lane
There's no built-in facility for that, short of log_statement = all.
You could try making a wrapper function: rename pg_terminate_backend
to something else and then make a plpgsql equivalent that issues
RAISE LOG and then calls the renamed function.
regards, tom lane
cuum to get to it.
regards, tom lane
table, so executing it requires locking all those tables
to make sure their schemas haven't changed.
regards, tom lane
ere may
have been some general system upgrades going on in the vicinity.
Reindexing all text indexes is the recommended remediation procedure
if you suspect a locale behavior change. There's some work afoot to
make PG notice the need for this automatically, but it's not done yet.
regards, tom lane
1-1
11
I concur with Daniel's suggestion that maybe "C" locale is
the thing to use for this data.
regards, tom lane
user-facing docs than it should've been.
I can agree with the position that all the weird little cases in
CheckIndexCompatible() are a bit much to be documenting, but not
changing the ALTER TABLE reference page at all seems inadequate.
regards, tom lane
we rely on.
regards, tom lane
[1] http://www.iana.org/time-zones
Matt Zagrabelny writes:
> Is there a continuation character for meta commands in psql?
No.
regards, tom lane
n.
https://www.postgresql.org/docs/current/datetime-config-files.html
regards, tom lane
anually
remove such files that haven't been accessed recently, but the
risk of human error is high.
regards, tom lane
attern to be a temp file. If you've got a
reasonable filesystem I wouldn't expect it to take long.
Maybe a minute? (but don't quote me on that)
regards, tom lane
ki/Slow_Query_Questions
regards, tom lane
potential helpers by refusing to follow the process.
regards, tom lane
not for PG (can't say about SQL Server). But see
suppress_redundant_updates_trigger here:
https://www.postgresql.org/docs/current/functions-trigger.html
regards, tom lane
ted during queries since you last
reset the statistics counters. By the time they're counted in this view,
they've already been deleted, so there's nothing for you to do here
maintenance-wise. But if the numbers seem to be going up a lot over time,
that might suggest that you look for the queries causing it.
regards, tom lane
gt; I'm getting the following errors in the log.
Unfortunately, this is pretty useless, since you only quoted the part
of the log after the problem was detected.
regards, tom lane
ow-update triggers on the table, or
child tables (either partitioning or traditional inheritance).
So I wonder what the schema of table "containers" looks like.
Or you could have hit some new bug ... but there's not enough
info here to diagnose.
regards, tom lane
about it the wrong way (better to look at preventing logins of privileged
accounts, and/or use of SQL permissions to limit what can be done).
If your objective is something else, you haven't explained what that is.
regards, tom lane
should be okay
if you update to 12.1. (There are some related issues that will be
fixed in 12.2, due out next week.)
regards, tom lane
gt; "DEALLOCATE" or "DEALLOCATE PREPARE".
I think you want double quotes not single quotes. The argument
of DEALLOCATE is an SQL identifier, not a string literal.
regards, tom lane
a SIGKILL. But if you
can get one, try to collect three or so and see if they're consistent.
Also, can you get an "EXPLAIN" of the query plan? (I imagine EXPLAIN
ANALYZE would trigger the crash, though verifying that might be worth
the time.)
regards, tom lane
r into a version without BYPASSRLS, the rest of the commands
would still work.
regards, tom lane
e connection drop,
or whether it stops sooner (and then there's some sort of timeout
before the error occurs).
regards, tom lane
LYZE after
> each bulk operation - and often that is precisely what happens..
> But - I am keenly aware that this cannot be performed within a transaction.
Plain ANALYZE can be, and that's all you need if the problem is to
update stats.
regards, tom lane
OPTION ]
[ GRANTED BY ]
so I suppose whoever added the implementation just forgot about
fixing the docs.
regards, tom lane
'm not sure if the latter
is possible, but for sure it'd be a large task if it is possible.
regards, tom lane
t;select md5(mycolumn) into local_variable from..."
A close reading of
https://www.postgresql.org/docs/current/storage-toast.html
would probably help you understand what's happening here.
regards, tom lane
TABLE foo ALTER COLUMN id TYPE varchar(100);
Hm, the code is supposed to avoid a table rewrite, but I wonder if
there's something else that's not being avoided, such as an index
rebuild or foreign-key verification. Could we see the whole table
definition, eg from psql \d+ ?
regards, tom lane
now, any index mentioning the altered
column has to be rebuilt.
Maybe someday we'll figure out how to do better. I'm kind of wondering
whether it wouldn't be safe to assume that changes that only change the
typmod and not the type OID don't require index rebuilds.
regards, tom lane
table "subsidiary object" so it only depends indirectly
on the extension that owns the table.
regards, tom lane
Marc Munro writes:
> On Mon, 2020-02-17 at 22:48 -0500, Tom Lane wrote:
>> An RLS policy is a table "subsidiary object" so it only depends indirectly
>> on the extension that owns the table.
> Yep, I get that, and I see the dependency chain in the catalog.
> Howe
thout knowing exactly what you're
doing is nil. (A specialist might not be successful either, of course.)
regards, tom lane
information that likely was
already cached at the start of function execution. But I think that's
a performance deficiency, not a bug per se.
regards, tom lane
agine
caching the info earlier during function execution, but as the comment
says, that has its own failure modes ... and they're more important
ones in practice. So I'm afraid there's not much to be done to
improve this.
regards, tom lane
gt; case the full restart did *not* help, but produced the same error again
> afterwards! I didn't reboot the whole box, though.
Hm ... that's interesting, but it sounds like you've systematically
expunged all the evidence about what happened :-(
regards, tom lane
n,
BEFORE triggers don't really know whether the insert is going to
happen. This doesn't even require use of upsert to cause a problem:
a later BEFORE trigger could prevent the insertion, or change what
gets inserted.
regards, tom lane
ny config changes in months.
> I didn't find much on-line about this. How concerned should I be? Would you
> move the instance to a different physical host?
Personally, I'd restart the postmaster, but not do more than that unless
the error recurs.
regards, tom lane
about developing hardware
problems. Both of these symptoms could be easily explained by dropped
bits in PG's shared memory area. Do you happen to know if the server
has ECC RAM?
regards, tom lane
w.i is null then
begin
new.i := whatever;
raise notice 'Supplied default';
end if;
This seems cleaner in principle, but a problem is that it can't tell
an inserted-by-default NULL from one that was intentionally supplied.
That might be OK if you never want the field to be null anyway.
regards, tom lane
Christophe Pettus writes:
>> On Feb 22, 2020, at 14:02, Tom Lane wrote:
>> It's a really bad idea to mark a function that has side-effects
>> (i.e., emitting a NOTICE) as immutable, especially if the occurrence
>> of the side-effect at well-defined times is exact
turning JIT off ("set jit = off") make the
problem go away?
There is as yet no "native" support for --with-llvm on macOS,
ie Apple themselves don't provide sufficient support for that.
EDB seem to have hacked up something that sort of works, but
only sort of.
regards, tom lane
this
sort of ability would be pretty useful, since otherwise you end up
with painful-to-rename duplicate output column names. And certainly
there is no particular need for this construct if you didn't write
a "*".
regards, tom lane
;s not actually necessary to sort on the relname (third sort key)
because your WHERE clause constrains that to be a constant. v11 saw
that but v12 fails to, which is a bug that I just pushed a fix for.
It's not terribly important for your example, but it might matter more
in other cases.
As far as solving your larger problem goes, I don't have any near-term
ideas other than to rewrite the query to look directly at the system
catalogs rather than going through information_schema views. But that's
not a very nice answer.
regards, tom lane
y think of a reason this would cause a problem,
at least not on 9.4 and up which use MVCC catalog scans. If you're
really still on 9.3 then it's notably more risky. In any case, I've
not had any caffeine yet today, so this doesn't count for much.
regards, tom lane
Marc writes:
> To whom do we report our findings regarding this issue ?
EDB is already on it:
https://www.postgresql.org/message-id/CA%2BOCxoz0bWi%2BR2WpocfkD20Lgrg69z1jQ_SZd-zmdzHW0zt%2Bbg%40mail.gmail.com
regards, tom lane
;s
a pain, and it will certainly not work if your homebrew libperl
isn't 100% ABI-compatible with the system one.
Personally I'd build plperl against the Perl you want to use it with.
The rest of PG isn't dependent on Perl, so you could use the community
install for the rest of it if you like.
regards, tom lane
Kouber Saparev writes:
> На чт, 27.02.2020 г. в 17:52 Tom Lane написа:
>> FWIW, I can't immediately think of a reason this would cause a problem,
>> at least not on 9.4 and up which use MVCC catalog scans. If you're
>> really still on 9.3 then it's notably m
ub-selects depend on
"num" somehow. Or possibly there's a way to do it without
a sub-select. On the whole this looks like a mighty expensive
way to generate a random string, so I'd be inclined to look
for other implementations.
regards, tom lane
Python folks ignoring it on purpose.
As an ex-Red-Hat employee, I am used to but nonetheless tired of
Red Hat haters. If you don't like their distro, fine, but don't
spread demonstrably false misinformation about it.
regards, tom lane
19 '::date;
date
2019-12-02
(1 row)
regards, tom lane
R, they're just hashes in Perl, so you should be able to do
stuff like iterating over the hash keys.
regards, tom lane
ssue is that jsonb "->" isn't leakproof,
so that clause falls back to a default selectivity estimate, and you
get a bad plan as a result.
regards, tom lane
just marking something "leakproof" when it isn't really so
is possibly a security problem. You should think twice about
what threat model you're hoping RLS will protect against.
regards, tom lane
fooling with the cost parameters and/or disabling seqscans.
regards, tom lane
Michael Lewis writes:
> On Fri, Sep 20, 2019 at 8:19 AM Tom Lane wrote:
>> There is a restriction on how many distinct GRANTs you can
>> issue against any one object --- performance will get bad if the ACL
>> list gets too large.
> Any ballpark numbers here? Are we t
he actual temp table size
each time, ie "select count(*) from temp_table" in between the
two steps, and then note whether the failures are correlated
with unusual temp table sizes?
regards, tom lane
being very complex, it
would break on a regular basis, because we change those structs often.
regards, tom lane
regards, tom lane
or writing some custom C code, because the
executor won't let you open a toast table as result relation :-(.
I wonder if we should change it to allow that when
allow_system_table_mods is true? This isn't the first time we've
seen people need to be able to do surgery on a toast table.
regards, tom lane
rdering". But your \d doesn't
show any index on startts. So maybe there's more than one amarkets
table?
Another possibly-useful bit of evidence is to see what EXPLAIN shows as
the query plan for this query.
regards, tom lane
e only way to make sure that you have validly encoded data.
However, if it's only one database out of a bunch, you could do something
like
* pg_dump that one database;
* drop said database;
* pg_upgrade everything else;
* restore that one database from dump.
regards, tom lane
501 - 600 of 2962 matches
Mail list logo
