On 06.12.21 15:50, Avi Weinberg wrote:
Does it mean that populating each table is done in a single
transaction? If so, when I have tables with foreign keys between them,
is it guaranteed that logical replication will populates the tables in
the proper order so the foreign key will be enforced?
On 07.12.21 08:51, Avi Weinberg wrote:
Just to clarify, they are disabled during initial sync only or are always
disabled on subscriber side?
Are all triggers disabled during initial sync or just foreign keys?
All triggers are by default disabled on replicas. See the ALTER TABLE
clauses DISA
On 07.12.21 17:10, Zwettler Markus (OIZ) wrote:
I did a pgbouncer configuration using the following ini file:
[databases]
* = host=localhost port=5433 auth_user=pgbouncer
Using the name "pgbouncer" for auth_user is buggy. Try using a
different name.
hen how can you expect it for yourself?
--
Peter Geoghegan
communicated with him online, and
I've worked on Postgres more or less full time for a full decade now.
As far as I'm aware he hasn't ever publicly posting to any of the
mailing lists.
--
Peter Geoghegan
g because of how they allow an
organization to deploy a program in a production environment, complete
with version control? Does it have something to do with decoupling the
mutable business data stored in tables from the programs contained/run
in the same database?
--
Peter Geoghegan
On 19.12.21 12:50, Shubham Mittal wrote:
2021-11-25 14:45:45.244 IST [18307] LOG C-0x6ae270:
sit/postgres@abcdlogin attempt: db=sit user=postgres tls=no
2021-11-25 14:45:45.299 IST [18307] LOG S-0x6b4f48: sit/postgres@abcd
new connection to server (from abcd)
2021-11-25 14:46:17.843 IST [18307]
On 12.01.22 12:16, Amine Tengilimoglu wrote:
I want to read an external pdf file from postgres. pdf file will
exist on the disk. postgres only know the disk full path as metadata. Is
there any software or extension that can be used for this? Or do we have
to develop software for it? Or w
On 16.02.22 14:27, Carsten Klein wrote:
AFAIK, this conversion is done by internal function float8in, which,
when called directly, yields the same results:
SELECT float8in('1.56');
--> 1.55 (wrong!) on one server, and
--> 1.56 (correct!) on all other servers.
fl
On 12/9/17 12:15, Boshomi DeWiki wrote:
> Python 2.7 will not be maintained past 2020.[1]
>
> Python2 is still default for Postgres 10. CREATE EXTENSION PLPYTHONU
> results in installation of PLPYTHON2U.
>
> As of now SUSE does not support PLPYTHON3U. (This will change soon)
>
> Are there any p
mportant to be
*exactly* compatible with EBCDIC order? As long as you're paying for a
custom collation, why not just use a collation that is helpful to
humans?
--
Peter Geoghegan
On Tue, Dec 12, 2017 at 5:18 AM, John McKown
wrote:
> On Tue, Dec 12, 2017 at 2:17 AM, Tsunakawa, Takayuki
> wrote:
>>
>> Hi Laurenz, Tom, Peter,
>>
>> Thanks for your suggestions. The practical solution seems to be to
>> override comparison operators of ch
n.com/nihujociga.sql
Kind regards
Peter
e the costs. If the
planner still gives you a sequential scan, then the index was not
applicable for other reasons.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Thanks Laurenz.
st major Linux
distributions. See:
https://github.com/petergeoghegan/amcheck
Note also that only this external version has the "heapallindexed" check.
--
Peter Geoghegan
provider without
accounting for pg_upgrade. If so, then that's a bug in the package. This is
a total speculation, but makes a certain amount of sense to me.
--
Peter Geoghegan
(Sent from my phone)
Please report this as a bug to the freebsd package maintainer.
--
Peter Geoghegan
(Sent from my phone)
avioral change in
OS collations, even though the OS collation behavior apparently did
not change.
I'm currently feeling too lazy to check that I guessed right about all
of this, but somebody should look into it.
--
Peter Geoghegan
Look into amcheck:
https://github.com/petergeoghegan/amcheck
--
Peter Geoghegan
(Sent from my phone)
pass "pg_index.indisprimary" as
"heapallindexed" argument, while generalizing from the example SQL
query for bt_index_check()). This process has a good chance of
isolating the problem, especially if you let this list see any errors
raised by the tool.
--
Peter Geoghegan
ot;indisprimary" as the heapallindexed argument. That way, only primary
keys would be verified against the heap, which is potentially a lot
faster.
--
Peter Geoghegan
On Sun, Dec 31, 2017 at 1:39 PM, Peter Geoghegan wrote:
> SELECT bt_index_check(index => c.oid, heapallindexed => true),
> c.relname,
> c.relpages
> FROM pg_index i
> JOIN pg_opclass op ON i.indclass[0] = op.oid
> JOIN pg_am am ON op.opc
org/gitweb/?p=postgresql.git;a=commit;h=a4ccc1cef5a04cc054af83bc4582a045d5232cb3
My guess is that that would make a noticeable difference, once v11
becomes available. Could you test this yourself by building from the
master branch?
--
Peter Geoghegan
ck on 9.4:
https://github.com/petergeoghegan/amcheck#redhatcentossles
I would start there.
--
Peter Geoghegan
your libedit library. Try
running psql with the -n option. If that helps, then look into building
psql with libreadline instead. Because libedit is terrible.
> - case 2 is fine
> - echo -n '≤' |hexdump -C
> e2 89 a4 |...|
&g
ad conversion happens, in other
> words it passes to the backend the ?? characters
> Any similar trick I could use on the postgres jdbc driver ?
That appears to be a completely separate issue.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
probably had a
locale of xx_XX.utf8, so it had the UTF8 encoding.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 1/22/18 17:11, Michael Krüger wrote:
> I do not fully understand the reasoning of making sequences
> transactional in the first place.
It was sequence DDL that was made transactional. Sequence use is still
nontransactional.
--
Peter Eisentraut http://www.2ndQuadra
traut.org/blog/2015/08/14/have-problems-with-postgresql-try-using-hive/
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
a
system table, then you'll get whatever types the system table uses.
There is nothing from with that.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
ld use the table
> some_schema.test_table_replication instead of
> public.test_table_replication?
No.
> I can't find any reference in the docs
> about tables or schemas in subscriptions.
Here:
https://www.postgresql.org/docs/10/static/logical-replication-subscription.html
--
changes, e.g.,
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/replication/logical/worker.c;h=eedc3a8816bc2f157e62a664bcc57b5f87530be9;hb=958fe549884928cd3bdf009993e9a05df5fd6cee#l1521
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
found in Kerberos database
Check that your DNS resolves everything correctly.
You can find some ideas about this error in the internet. It's not a
problem specific to PostgreSQL. It looks like you have things set up
correctly.
--
Peter Eisentraut http://www.2ndQuadrant.com/
lso says that you should handle it
nonetheless. ;-)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
t up in a way that it
blows away your database on each run.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
ut the modules themselves are still extensions that you need
to install into the databases.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
sistently:
https://www.youtube.com/watch?v=p5RaATILoiE&t=2079s
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f21668f3
Of course, this is only a guess. I vaguely recall a complaint that
sounded vaguely like yours, also involving partitioning.
--
Peter Geoghegan
On Wed, Oct 30, 2024 at 11:39 AM Don Seiler wrote:
> Thanks Peter, I'll look into that shortly.
It sounds like you have no updates and deletes. Right? So the only
thing that could be different is the way that the pages are being
split (aside from variations in the width of index tuple
ind by fillfactor in each case. In
general page splits tend to come in distinct "waves" after CREATE
INDEX is run.
--
Peter Geoghegan
tually make any promises about not getting unique
violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
make such a promise. That's the main reason why Postgres supports
both.
--
Peter Geoghegan
NFLICT DO UPDATE.
--
Peter Geoghegan
Seems too easy but have you tried
psql -p 6432 -h localhost words_de
?
On Sun, Dec 1, 2024 at 3:59 PM Alexander Farber
wrote:
> My problem is related to
> https://github.com/docker-library/postgres/pull/440/files
>
> But I am yet not sure how to enable listening at localhost again
>
es with a generic table name
alias?
--
Peter Geoghegan
On 23.04.25 11:14, Daniel Gustafsson wrote:
On 23 Apr 2025, at 09:16, Laurenz Albe wrote:
On Wed, 2025-04-23 at 00:21 -0500, Igor Korot wrote:
However, this page
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY
describes both default and mn/max
ll
isn't quite the same as Perl (And I suspect it's the same for Python).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hj
At least that was the situation 10 years ago. These days much software
is offered as a service. If the customer sees only a REST API and
doesn't have to host the database on their own servers, they won't care
about the RDBMS underneath.
hp
--
_ | Peter J. Holzer
On 2018-04-27 22:52:39 +, g...@luxsci.net wrote:
> Perhaps I'm extreme. In my ideal world, developers might not even know table
> names! I'm kidding ,sorta...
If they don't know the table names, how can they write those stored
procedures?
hp
--
_ | Pe
On 2018-04-28 09:54:27 -0500, Steven Lembark wrote:
> On Sat, 28 Apr 2018 08:02:21 +0200
> "Peter J. Holzer" wrote:
>
> > On 2018-04-27 22:52:39 +, g...@luxsci.net wrote:
> > > Perhaps I'm extreme. In my ideal world, developers might not even
>
23:17:44+00'::timestamptz)
will still return 7200, even though I have explicitely specified a UTC
timestamp.
What your check probably does is to enforce that the client's time zone
is set to UTC.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) |
ATATYPE-DATETIME-INPUT
>
> "For timestamp with time zone, the internally stored value is always in UTC
> (Universal Coordinated Time, traditionally known as Greenwich Mean Time,
> GMT)"
This is not actually true. There is nothing in the storage format which
depends on UTC (wel
e
the query so that it creates several shorter strings instead.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -
bound and what exactly the "nice value" affects. The best way to find
out is probably to try it.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at
27;subjectname'],
is_pe_or_nstp,))
A bit more readable, IMHO.
Alternatively, import the complete table *unchanged* from MySQL (you can
use copy_from() for this which is much faster than individual inserts),
and then convert it with a single SQL statement.
hp
--
ta is the
| only way to communicate changes between different WITH sub-statements
| and the main query.
--
https://www.postgresql.org/docs/10/static/queries-with.html#QUERIES-WITH-MODIFYING
In a DO block the statements are processed sequentially and each
statement sees the results of the
t;. I can easily
get the required partial order in the application. But I'd like to
understand what the optimizer is doing here.
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.a
allow the user to use a real text editor instead of a text area.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/
On 2018-07-19 11:43:18 -0600, Rob Sargent wrote:
> On 07/19/2018 11:04 AM, Peter J. Holzer wrote:
> > On 2018-07-18 08:09:35 +1000, Tim Cross wrote:
> > > If using web widgets to author content on the wiki is the main
> > > impediment for contributing content, mayb
uot;hardware cluster" is. Probably some kind of
appliance which packages two nodes, some storage and the HA software.)
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at
blspc/* on the release server
> - start postgres on both servers
If you copy the whole database anyway before deleting the tablespace:
Why don't you just drop the 600 GB table on the release server?
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_
uot; on output while Pg10
prints the more precise (but still not exact) "2.2005".
(I would argue that the Pg9.3 output is better, since it represents the
same value in fewer digits, but always printing the minimum number of
digits necessary is surprisingly difficult.)
hp
-
ap alone, and you are only overcommitting if you exceeded the size
of the sum. The overcommitment in Linux is of a different kind: Linux
uses copy on write whereever it can (e.g. when forking processes, but
also when mallocing memory), and a CoW page may or may not be written in
the future. It only need
if that matters or not.
It may or may not. Personally I prefer to use find -mtime (or logrotate,
or cleandir, or keepfree, ...) to avoid the irregularities of the
Gregorian calendar.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) |
d 4 are about 33 % faster than 2. But
there is a still quite a respectable performance boost.
hp
PS: The script is of course in the same repo, but I didn't include the
test data because I don't think I'm allowed to include that.
--
_ | Peter J. Holzer| we build
uces an
error almost 50 times larger.
> I'm not really convinced that doing it like this rather than doing the
> standard conversion is a good idea. You can't manufacture precision
> where there is none
It may be that the real value of that number is only known to +/- 0.1.
Or
On 2018-10-18 18:58:13 -0400, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > On 2018-10-18 10:15:40 -0400, Tom Lane wrote:
> >> You could ju-jitsu the system into duplicating that behavior by casting
> >> to text (which invokes float4out) and then to numer
an SQL null is converted to JSON null. Returning SQL null instead of a
JSON null breaks that expectation for no discernible reason. It also
isn't consistent, since an SQL null in an array or composite is
converted to a JSON null, as I would expect.
hp
--
_ | Peter J. Holzer
olding information, but not your
data tables or indexes. Your 18 TB table will definitely not be duplicated
during the upgrade if you can use --link.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophist
On 2019-07-19 11:37:52 -0400, Matthew Pounsett wrote:
> On Fri, 19 Jul 2019 at 11:25, Peter J. Holzer wrote:
>
> On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote:
> > Okay. So I guess the short answer is no, nobody really knows how to
> > judge how much spa
full (or if
they expire). Is this not a problem in your case or did you make sure
that this cannot happen?
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | mana
ys the same", our test was green 99% of the time, so we
> discarded it as flaky.
>
> Fuzzy testing could be an option, but this would go too far, as for
> Peter extension suggestion. We have huge existing codebase with more
> than 10K tests, and I do not want to modify our whole
Gbit to
different switches, switches connected by 10 Gbit). The difference
between a 1024 byte buffer and a 1460 byte buffer is small but
measurable. Anything larger doesn't make a difference. So increasing the
buffer beyond 8 kB probably doesn't improve performance on a 1 Gbit LAN.
I
e database's default tablespace before
> using this command.
What's the reason for this error? Wouldn't it be simpler to leave
relations alone which are already in the correct tablespace?
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters no
g_restore, ! /usr/local/bin/psql
> PGBACKUPUSERS backup_host = PGBACKUP
This is the wrong way around. It should be something like
alice, bob = (pg_backup_username) /usr/local/bin/pg_dump
(Apologies if I didn't get the syntax right. Slogging through the sudoes
manual reminded me why I wrot
home/postgres/9.6" "/home/postgres/9.6/data" 5432 DEFAULT: FATAL:
> invalid byte sequence for encoding "UTF8": 0xeb 0x2f 0xdb
0xeb 0x2f 0xdb is indeed not valid UTF-8. So whereever this sequence
comes from isn't UTF-8 encoded. In ISO-8859-1 that sequence would be
&
ractice (although I've seen a lot of
other problems caused by people who made unwarranted assumptions about
email addresses).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticat
think is even worse: If I don't see
any original content within the first 100 lines or so I usually skip the
rest).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at
orrendously slow.
Even if you do know this, you often have to bend over backwards to
get reasonable performance.
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
signature.asc
Description: PGP signature
ent production databases interfering with each
other. Also, if you have the test and production database on the same
host, there are some procedures which you can't safely test (e.g. an OS
upgrade).
I would think about putting each database in virtual machine or at least
a container, though.
On 2019-08-24 13:22:38 +0200, Luca Ferrari wrote:
> On Sat, Aug 24, 2019 at 11:53 AM Peter Wainaina wrote:
> >
> > Thanks much for the response. This is what I mean am a database
^
> > administrator for a produ
a different language" way.
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/
On 2019-08-27 08:16:08 -0700, Adrian Klaver wrote:
> Django takes Postgres as it's reference database which makes things easier,
> especially when you add in
> contrib.postgres(https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/)
Looks nice.
hp
--
_ | P
in Oracle's sqlplus), so that the user can decide to display a specific
column (or maybe all float numbers) as (for example) "%8.3f" or ".6e".
This is of course already possible by using to_char in the query (e.g.
to_char(f, '.999') or to_char(f, '9.9
ouldn't access files directly, just talk to the server via the
socket.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.h
On 2019-09-09 13:29:38 +0200, Daniel Verite wrote:
> Peter J. Holzer wrote:
> > 2) Why does psql need to read postgresql.conf, and more specifically,
> > why does it care about the location of the data directory? It
> > shouldn't access files directly, just
On 2019-09-09 19:15:19 +0200, Peter J. Holzer wrote:
> On 2019-09-09 10:03:57 -0400, Tom Lane wrote:
> > "Peter J. Holzer" writes:
> > > Yesterday I "apt upgrade"d patroni (to version 1.6.0-1.pgdg18.04+1
> > > from http://apt.postgresql.org/pub/rep
he index, so the fact the fact that the index is a
bit larger shouldn't make a difference.
Explain shows that the row estimates are spot on, but the cost for using
t_a_b_idx is higher than for t_b_idx (which is in turn higher than for
t_b_a_idx).
hp
--
_ | Peter J. Holzer
On 2019-09-12 12:54:55 -0400, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > we'll consider just three columns, which we unimaginatively call a, b,
> > and c. There are also three indexes:
>
> > t_a_idx btree (a) WHERE a IS NOT NULL
> >
On 2019-09-12 21:04:25 +0200, Peter J. Holzer wrote:
> On 2019-09-12 12:54:55 -0400, Tom Lane wrote:
> > It's not taking the partial-index filter into account in that, I
> > suspect, which skews the results in this case --- but that would be
> > hard to account for accu
0.333 ║
║ -2 │ 3 │5 │3 │ 1.67 ║
║ 3.1415926536 │ 2.71828 │ 0.4233126536 │ 3.1415926536 │ 0.134744602587137 ║
╚══╧═╧══╧══╧═══╝
(3 rows)
No idea whether this is more or less efficient than
On 2019-09-13 11:49:28 +0900, Kyotaro Horiguchi wrote:
> At Thu, 12 Sep 2019 23:16:01 +0200, "Peter J. Holzer"
> wrote in <20190912211601.ga3...@hjp.at>
> > On 2019-09-12 15:35:56 -0500, Ron wrote:
> > > On 9/12/19 2:23 PM, stan wrote:
> > > >
On 2019-08-15 16:56:57 -0400, stan wrote:
> bossiness constants
On 2019-09-02 13:31:14 -0400, stan wrote:
> bossiness plan
> bossiness model
On 2019-09-13 05:57:33 -0400, stan wrote:
> bossiness work
I'm starting to think that this is not a typo :-)
SCNR,
hp
--
nload themselves for free I don't know).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
signature.asc
Description: PGP signature
won't have to enter their
passwords again. But I think this works only if the client and the
server are on the same host. And you still have to maintain the groups,
although that should be easy to automate.
hp
--
_ | Peter J. Holzer| we build much bigger, better di
Time: 0.924 ms
wds=> select replace('steven', 'e', NULL);
╔═╗
║ replace ║
╟─╢
║ (∅) ║
╚═╝
(1 row)
Time: 0.918 ms
Throwing an exception for a pure function seems "un-SQLy" to me. In
particular, jsonb_set does something similar for json values as replac
the JSON object with an SQL NULL (i.e.
unknown) which returns SQL NULL:
wds=> select jsonb_set('{"a": 1, "b": 2}'::jsonb, '{c}', NULL);
╔═══╗
║ jsonb_set ║
╟───╢
║ (∅) ║
╚═══╝
(1 row)
hp
--
_ | Peter
on to a few words and "see Section 9.9.x". So you basically
have to read the text and not just the table. Maybe that would make
sense for the json functions, too?
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) ||
On 2019-10-22 18:06:39 -0700, David G. Johnston wrote:
> On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer wrote:
> On 2019-10-20 13:20:23 -0700, Steven Pousty wrote:
> > I would think though that raising an exception is better than a
> > default behavior which delete
t; although the advantage of it vs using a concat operator is slim.
True. However, concatenation of string literals by juxtaposition isn't
specific to SQL. Two other well known languages where this works (even
without a newline) are C and Python.
hp
--
_
on (like directory layout, etc.), so
it should be simpler and safer than invoking pg_upgrade yourself (and
pg_upgrade is hidden in /usr/lib/postgresql/*/bin to prevent you from
invoking it accidentally).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) |
301 - 400 of 978 matches
Mail list logo
