> On Apr 9, 2018, at 07:33, Thomas Poty wrote:
>
> ok, and long answer ? is it random?
It's not literally random, but from the application point of view, it's not
predictable. For example, it's not always the one that opened first, or any
other consistent measure.
never cleaning it up. This is
PostgreSQL 9.6.6 on Windows using the EDB installer. We can't seem to see
anything unusual about the configuration of the server (archive_mode = on, but
it's otherwise properly operating as a secondary).
--
-- Christophe Pettus
x...@thebuild.com
has served on the board of the Django Software
Foundation:
1. The Django Code of Conduct is considered a success and a valuable asset to
the growth and health of the community.
2. Others involved in the event mentioned above would not describe it in the
same terms
--
-- Christophe Pettus
x...@thebuild.com
mmunity due to harassment, and there was no system within the
community to report and deal with that harassment.
What we do have is 20 years of people demonstrating reasonable good judgment,
which we can conclude will apply to a CoC committee as well.
--
-- Christophe Pettus
x...@thebuild.com
de commits)
have careers and a reasonable expectation of privacy, it's possible that the
reason you have heard this upteen times is that there are issues in the
community that you are not aware of. I would say that it more likely that bad
faith and conniving on the part of upteen peo
r Chamber that is answerable only to itself. It also allows
for an appeal mechanism.
--
-- Christophe Pettus
x...@thebuild.com
er profession can do is substantial.
--
-- Christophe Pettus
x...@thebuild.com
seems a very strange and specific hill to choose to die on in
this discussion.
--
-- Christophe Pettus
x...@thebuild.com
e actual situations between real people. It's not their job to
define values; it's their job to resolve situations. In my experience in
dealing with CoC issues, the situations (while often complex) are rarely of the
form, "This word does not mean anything bad where I come from."
--
-- Christophe Pettus
x...@thebuild.com
IDE comparing to, say, pgAdmin4. There's no
reason we can't have multiple IDEs, of course, but when I think of an
"integrated development environment," I think of something (along the lines of
the JetBrains family) that handles the full stack, including debugging.
--
-- Christophe Pettus
x...@thebuild.com
nd1_var, result_var,
select_div_scale(&operand_var, &bound1_var),
true);
... produces 0. for that particular value, instead of 1,
and the subsequent +1 and FLOOR() result in 1 instead of 2.
--
-- Christophe Pettus
x...@thebuild.com
ts function is to add tables
that were added to the publication after the subscription was last created or
refreshed. It does (by default) copy the data from newly-added tables, but it
does not resync the data from the existing tables.
--
-- Christophe Pettus
x...@thebuild.com
n the source actually cause anything to
break, as opposed to a delay while the checkpoint completes? The current
situation can create a corrupted target, which seems far worse than just
slowing down pg_rewind.
--
-- Christophe Pettus
x...@thebuild.com
734e74352...@thebuild.com
--
-- Christophe Pettus
x...@thebuild.com
ed to create an expression
index; then, the value lasts as long as the index does. The best way to think
of an IMMUTABLE is that it is a pure function, unchanged by system state.
(This is one of the reasons that datetime-related functions are often STABLE
rather than IMMUTABLE, due to
complete, but how it performs on any particular database will
vary considerably.
The good news is that for a database of that size, it's easy to install it and
test it against your workload. That's really the only practical way of telling
if it will be suitable for you.
-
able, and that can make it hard to schedule
and manage maintenance windows. What we were told previously is that RDS
queues the operations, and it can take a variable amount of time for the
operation to be worked on from the queue. Is that not the case?
--
-- Christophe Pettus
x...@thebuild.com
> On Sep 17, 2018, at 07:09, Igor Korot wrote:
>
> Is there a way to query a server for a place where the log file is?
SHOW log_directory;
It's either relative to the PGDATA directory, or an absolute path.
--
-- Christophe Pettus
x...@thebuild.com
> On Jan 8, 2020, at 15:56, github kran wrote:
>
> Great I see its supported based on the link but the problem is we are locked
> into a Aurora RDS and we can't use logical replication on that engine.
You can use pglogical on RDS PostgreSQL 9.6.
--
-- Chris
> On Feb 5, 2020, at 18:54, Vik Fearing wrote:
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
+1.
database,
and shouldn't be created unless there is a compelling need for them./
--
-- Christophe Pettus
x...@thebuild.com
il mail.
https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
--
-- Christophe Pettus
x...@thebuild.com
an, of course, create a PL/pgSQL function and use that as the default.
--
-- Christophe Pettus
x...@thebuild.com
se notice 'Supplied default';
return 1;
end;
$$ immutable language plpgsql;
xof=# create table t ( i integer default supply_default(), t text );
CREATE TABLE
xof=# insert into t(i, t) values (2, 'text');
INSERT 0 1
xof=# insert into t(t) values ('text');
NOTICE: Suppli
, and it doesn't actually need to be immutable here; just cut and pasted
from the wrong example.
(That being said, I'm not coming up with a specific bad thing that a RAISE
NOTICE in an immutable function will cause. Is there one?)
--
-- Christophe Pettus
x...@thebuild.com
ing execution.
Ah, good point. My solution also does assume that a DEFAULT expression is only
evaluated if the default is required, and that behavior isn't (afaik) a promise.
--
-- Christophe Pettus
x...@thebuild.com
of trigger-related info.
--
-- Christophe Pettus
x...@thebuild.com
it is running; what allows it to bring the database back up to consistency is
the write-ahead log segments that are created while pg_basebackup is running.
That's why it is important to have all of the WAL segments created during the
run (which is what --wal-method=stream provides you).
--
-- Christophe Pettus
x...@thebuild.com
access to the filesystem of an RDS server).
--
-- Christophe Pettus
x...@thebuild.com
actual binary
object, into the database... and that's still good advice, even on RDS! It
just means that path needs to be a URI or some other piece of metadata that
points to a different server, rather than the RDS server.
--
-- Christophe Pettus
x...@thebuild.com
Y within the trigger to wake up a process that
is waiting on NOTIFY.
--
-- Christophe Pettus
x...@thebuild.com
le, to prime staging environments or
developer systems), the outbound transfer can add up fast, too.
--
-- Christophe Pettus
x...@thebuild.com
LL ON TABLES FROM xyuser;
ALTER DEFAULT PRIVILEGES
db=> \ddp+
Default access privileges
Owner|Schema | Type |Access privileges
+---+--+--
xyuser | | table|
d
r of
database objects being dumped.
--
-- Christophe Pettus
x...@thebuild.com
> On May 31, 2020, at 13:10, t...@exquisiteimages.com wrote:
>
> On 2020-05-31 13:08, Christophe Pettus wrote:
>>> On May 31, 2020, at 08:05, t...@exquisiteimages.com wrote:
>>> My pg_class table contains 9,000,000 entries and I have 9004 schema.
>> Which v
> On May 31, 2020, at 13:37, Adrian Klaver wrote:
>
> Just a reminder that the OP's original issue was with using pg_upgrade.
True, although IIRC pg_ugprade uses pg_dump under the hood to do the schema
migration.
--
-- Christophe Pettus
x...@thebuild.com
> On Jun 2, 2020, at 13:30, Stephen Frost wrote:
>
> Eh, that's something that I think we should be looking at supporting, by
> using FDWs, but I haven't tried to figure out how hard it'd be.
Being able to access a FDW that way would rock.
--
-- Christophe Pettus
x...@thebuild.com
ant
manual line is:
"When (-C / --create) is used, the database named with -d is used only
to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is
restored into the database name that appears in the archive."
--
-- Christophe Pettus
x...@thebuild.com
level of support you need on this list,
for free.
--
-- Christophe Pettus
x...@thebuild.com
sional_support/
--
-- Christophe Pettus
x...@thebuild.com
ly in the database, rather than using dump/restore as a way of changing
ownership all at once. This is not infrequent when you have an existing
database in which a superuser owns everything
--
-- Christophe Pettus
x...@thebuild.com
r-defined objects which are
owned by the user "postgres"; you should change those to the user that you
want, leaving the CASTs owned by "postgres".
--
-- Christophe Pettus
x...@thebuild.com
e owner of internal types to make sure that my CAST
restores work." That strikes me as a *very* ad hoc feature indeed.
--
-- Christophe Pettus
x...@thebuild.com
e new database once it is restored. This can be
done entirely with existing tools, and doesn't need any changes to pg_restore,
or even having to do ownership changes of internal types (which I strongly
suspect will bite you later).
--
-- Christophe Pettus
x...@thebuild.com
Seq Scan on t (cost=0.00..29.90 rows=1990 width=13) (actual time=0.004..0.004
rows=0 loops=1)
Planning Time: 0.034 ms
Execution Time: 0.015 ms
(3 rows)
--
-- Christophe Pettus
x...@thebuild.com
ans. The performance hit wasn't huge, but they were being joined
to some *very* large tables, and the differences added up.
--
-- Christophe Pettus
x...@thebuild.com
less common, I would assume, that it results in a bad plan), but it did in this
case.
--
-- Christophe Pettus
x...@thebuild.com
on disk, that certain
seems straight-forward, and no *more* hacky than the current situation.
--
-- Christophe Pettus
x...@thebuild.com
ere is modest: CREATE INDEX CONCURRENTLY needs to lock the table
briefly at a couple of points in the operation, and dropping the old index
requires a brief lock on the table. It is, however, much less overall lock
time than REINDEX would be.
--
-- Christophe Pettus
x...@thebuild.com
ot;catch up" the archiver such that it is operating on files that
actually exist; besides setting archive_command to '/bin/true' and letting it
chew through the old ones, is there a way of safely advancing the position of
the archiver?
--
-- Christophe Pettus
x...@thebuild.com
> On Aug 6, 2020, at 18:45, Jerry Sievers wrote:
> Deleting the .ready file should allow the archiver to get past the
> missing file.
Ah, excellent, yes.
--
-- Christophe Pettus
x...@thebuild.com
cf5a71e5a5c
--
-- Christophe Pettus
x...@thebuild.com
very large random keys) do have the advantage that they are
somewhat self-secure: You can expose them to outsiders without having to worry
about other keys being guessable.
--
-- Christophe Pettus
x...@thebuild.com
> On Sep 13, 2020, at 23:09, Matthias Apitz wrote:
> Why a SELECT COUNT(*) of the
> full table takes around 1 minute:
There's an explanation here:
https://wiki.postgresql.org/wiki/Slow_Counting
--
-- Christophe Pettus
x...@thebuild.com
They are not required to do
so. If you cannot persuade them, and are not in a position to pay them, then
that's a reality you'll just have to accept.
--
-- Christophe Pettus
x...@thebuild.com
that validates it.
--
-- Christophe Pettus
x...@thebuild.com
saw him at events.
--
-- Christophe Pettus
x...@thebuild.com
t keys being in a consistent order.
You'll want to have a step when ingesting the JSON object into a report that
lines up the key values appropriately with the right presentation in the report.
--
-- Christophe Pettus
x...@thebuild.com
able once
created. (If you run it through a JSONB-expecting function, then the ordering
may change again.) It's less efficient to operate on than JSONB, but that
might be OK for your purposes.
--
-- Christophe Pettus
x...@thebuild.com
cluster that was initialized with an older major version. You'll need to do a
pg_dump/pg_restore, or use pg_upgrade to create a new cluster.
--
-- Christophe Pettus
x...@thebuild.com
TNAME} -i "${CSVPATH}copycmd.z"
--
-- Christophe Pettus
x...@thebuild.com
rated column.
https://www.postgresql.org/docs/current/ddl-generated-columns.html
--
-- Christophe Pettus
x...@thebuild.com
> On Dec 30, 2020, at 11:48, Christophe Pettus wrote:
>
> This might be a good use for a generated column.
>
> https://www.postgresql.org/docs/current/ddl-generated-columns.html
I take that back; the generation formula has to be immutable as well. Perhaps
a column
. For example, in PL/pgSQL:
IF NEW.is_canceled IS NOT DISTINCT FROM OLD.is_canceled THEN
NEW.is_canceled := etc etc ;
ENDIF;
There's currently no way to detect if the column was simply not mentioned at
all in the UPDATE statement.
--
-- Christophe Pettus
x...@thebuild.com
xecution of these triggers matters; PostgreSQL
executes triggers at the same level alphabetically.
Now, this is a pretty high-overhead way of handling it, and it is probably
better to see if there is an application logic change that can happen here.
Best,
--
-- Christophe Pettus
x...@thebuild.com
ces, in part because
(IIRC) Oracle makes it an incredible pain in the neck to add tablespaces once
the DB is in use. For sharding purposes, you probably want schemas in
PostgreSQL instead of tablespaces, although having that many schemas is going
to not be optimal, either.
--
-- Ch
u just want fuzzy searching on a small number of words,
trigrams are probably fine; for more sophisticated kinds of searching, you want
tsvector.
They're completely different sets of functionality in PostgreSQL.
--
-- Christophe Pettus
x...@thebuild.com
system catalogs is based on the
parsed version of the view definition, rather than the literal text you
entered. Generally, you maintain your view definition separately in a source
code control system in its original form.
--
-- Christophe Pettus
x...@thebuild.com
tLab, or one of (many!) other tools
or products to store the view definition.
This has benefits besides just retaining the original source code, as you
mention below: Version control, tracking, issue management and commit merging,
etc.
--
-- Christophe Pettus
x...@thebuild.com
source code
repository, but it becomes very useful very quickly.
--
-- Christophe Pettus
x...@thebuild.com
This conversation doesn't really have anything to do with monitoring and
fine-tuning PostgreSQL, at this point, does it? It might be appropriate to let
go the meta-discussion when each individual person thinks it is appropriate to
answer a question.
--
-- Christophe Pettus
Conduct
process:
https://www.postgresql.org/about/policies/coc/
--
-- Christophe Pettus
x...@thebuild.com
people who are not
deep experts a chance to answer the question.
--
-- Christophe Pettus
x...@thebuild.com
etty wild all by itself.
--
-- Christophe Pettus
x...@thebuild.com
x27;t know the resources out there.
I would assume they are acting in good faith. If you politely point out
resources to them and they get snappish, then it can become a CoC issue.
Otherwise, I think that being generous in what we receive and accurate in what
we reply, as with any protocol,
simple integer.
Interval represents more than just a count of seconds or milliseconds, or some
other unit; it also includes intervals that are not a fixed number of seconds,
such as months and years.
--
-- Christophe Pettus
x...@thebuild.com
e session state:
https://www.postgresql.org/docs/current/sql-reset.html
You probably want to do this rather than try to intercept every single
operation in order to the reset at the end.
--
-- Christophe Pettus
x...@thebuild.com
you
can't do UPDATE / SELECT FOR UPDATE / FOR SHARE) to start spilling to disk?
--
-- Christophe Pettus
x...@thebuild.com
ry need to read them? Visibility?
--
-- Christophe Pettus
x...@thebuild.com
members of the multixact, and if the multixact structure has spilled to disk,
that gets very expensive.
--
-- Christophe Pettus
x...@thebuild.com
> On Jun 12, 2022, at 23:07, Pavel Stehule wrote:
> The lazy implementation theoretically can be possible, but why?
Isn't one of the reasons for the savepoint (in fact, the principal reason) to
reset the connection back to non-error state so that execution can continue?
In that case, it rea
> On Jun 28, 2022, at 18:41, Bryn Llewellyn wrote:
> Should I simply understand that when I have such a dynamic dependency chain
> of "immutable" functions, and should I drop and re-create the function at the
> start of the chain, then all bets are off until I drop and re-create every
> func
> On Jun 28, 2022, at 23:42, Laurenz Albe wrote:
> That is not enough in the general case. You are not allowed to redefine
> an IMMUTABLE function in a way that changes its behavior [...]
I think "not allowed" is putting it too strongly. It would be a bit much to
ask that every single user-
> On Jul 5, 2022, at 11:12, Bryn Llewellyn wrote:
> Finally, what do you think of a possible future enhancement to allow setting
> a null search_path?
You use the empty string, rather than NULL, but it works right now:
xof=# show search_path;
search_path
-
"$user", pub
> On Jul 5, 2022, at 22:35, Matthias Apitz wrote:
> Internally, in the DB layer, the read_where() builds the row list matching
> the WHERE clause as a SCROLLED CURSOR of
>
>SELECT ctid, * FROM d01buch WHERE ...
>
> and each fetch() delivers the next row from this cursor. The functions
> s
> On Jul 6, 2022, at 09:48, Bryn Llewellyn wrote:
> Neither causes an error. The "show", in each case, prints the bare value with
> no quotes. It never struck me try try double quotes around the timezone
> argument. I'm shocked that they are silently accepted here and seem to have
> the same
> On Jul 6, 2022, at 12:51, Matthias Apitz wrote:
> it is uniqu to identify a row in a table once
> known.
I think the point that we are trying to make here is that a ctid *isn't* that.
There is no guarantee, at all, at any level, that the ctid of a row will remain
stable, not even between
> On Jul 18, 2022, at 20:03, merryok wrote:
>
> I've read the doc, and it doesn't help too much.
> Finally I've found START_CRIT_SECTION and END_CRIT_SECTION. It's like
> mtr.start(), mtr.commit() in mysql. May I ask why many places are wrapped
> into START_CRIT_SECTION/END_CRIT_SECTION duri
> On Jul 28, 2022, at 18:04, Bryn Llewellyn wrote:
> Is this expected?
Yes. This isn't a bug.
> In other words, is there a careful explanation of what "parse" means in the
> context of "create or replace" for a subprogram that predicts all of the
> outcomes that I reported here?
Database
> On Jul 28, 2022, at 18:49, Bryn Llewellyn wrote:
> It's this that surprises me. And it's this, and only this, that I'm asking
> about: might _just_ this be a fixable bug?
It might be surprising, but it's not a bug. You can demonstrate it with a very
small test case:
CREATE FUNCTION f() R
> On Aug 3, 2022, at 10:16, zaphod61 wrote:
>
>
> I've inherited a postgresql 12.3 installation. It has 1 database in it. I
> need to upgrade it to the newest version, which appears to be 12.11. Can I
> just download the installer for 12
> 11 and run that to upgrade the product and still m
> On Aug 17, 2022, at 13:06, Ivan N. Ivanov wrote:
>
> How to speed up recovering of WAL files?
Since you are running on your own hardware, you might take a look at:
https://github.com/TritonDataCenter/pg_prefaulter
> On Aug 17, 2022, at 22:57, Abdul Qoyyuum wrote:
> Question is, do we have to shutdown traffic and close all existing open
> connections in order to drop and properly recreate the index?
No, you don't.
On the CREATE INDEX CONCURRENTLY command, what is likely going on is that when
the conne
I am reasonably sure the answer to this is "no", but can the oldest xmin that
hot_standby_feedback sends to the primary also delay SIReadLocks cleanup?
Going through the code, it looks like they're independent, but this isn't a
part of the system I know particularly well.
> On Aug 20, 2022, at 15:42, Perry Smith wrote:
>
> To rephrase, is it possible to write a view that would work from the child
> terms out towards the ancestors?
Assuming that the concern is that you want to parameterize this predicate:
WHERE basename = '10732.emlx'
... you mig
> On Aug 25, 2022, at 21:43, Bryn Llewellyn wrote:
> [...]
I've read this a few times, and I am having trouble understanding what behavior
you were expecting out of PostgreSQL, and what behavior you received that you
didn't think was correct. If it is "pg_constraint has a column connamespac
> On Aug 26, 2022, at 15:33, Bryn Llewellyn wrote:
> [...]
I'm still not clear on what you are proposing. Are you proposing a change to
PostgreSQL to remove the "connamespace" column from the "pg_constraint" table,
since it can be derived from other tables?
> On Aug 26, 2022, at 18:47, Bryn Llewellyn wrote:
> No, I’m not proposing any code change.
Thanks for clarifying.
> On Sep 1, 2022, at 10:14, V wrote:
> I want new/old tuples with pgoutput. how?
I assume here you are reading the pgoutput protocol directly.
Logical decoding sends out two tuple structures:
1. The replica identity of the row (in the case of update and delete).
2. The new row data (in the c
> On Sep 1, 2022, at 21:41, Meera Nair wrote:
> Archival hangs. Is this expected?
> postgres=# select pg_start_backup('test', true, false);
> pg_start_backup
> -
> 1/F960
> (1 row)
>
> postgres=# select pg_stop_backup('f');
> NOTICE: base backup done, waiting for required
1 - 100 of 340 matches
Mail list logo
