On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson wrote:
> psql -Xc "select datname from pg_database WHERE datname \!~
> 'template|postgres' ORDER BY datname;"
>
Remove the space:
psql -Xc "select datname from pg_database WHERE datname!~
'template|postgres' ORDER BY datname"
I'm not really sure why
On Wed, Jul 10, 2024 at 2:59 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> And what are the first few lines of the file? Use text, not screenshots.
>
Yes the line with 'INSERT'
grep -ni 'INSERT INTO' scripts/insert-addrs.sql
--
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪
If the file has these line breaks you show, then can make it to multiple
'INSERT INTO' instead.
Search for lines starting with parentese begin '(' and replace it with the
correct INSERT and last comma to semi-colon:
cat i.sql | sed -e 's/^(/INSERT INTO foo VALUES(/' -e 's/,$/;/'
Does the file c
Hi
On my test server I have Oracle Linux 8.10 installed.
Here I have installed postgresql 16.1 from postgresql.org repository.
Upgrade to Oracle Linux 9:
When doing a »leapp preupgrade --oraclelinux« I get the message below.
I want to have postgresql.org as my repo for PostgreSQL and Oracle Linu
> test3 | troels | UTF8 | libc| en_US.utf8 |
en_US.utf8
It is wrong but I guess it's working?
how did you create test3?
On Fri, May 17, 2024 at 2:44 PM Troels Arvin wrote:
> Hello,
>
> In a Postgres installation, I have databases where the locale is
> slightly different
Have you tried
sudo -u postgres psql
On Tue, Apr 23, 2024 at 2:14 PM Arbol One wrote:
> Hello.
> In my Debian box, after entering this command to psql-16, *psql -h
> localhost -U postgres*
>
> psql-16 asks for the password, which I have forgotten.
> So I get this message:
>
>
>
> *psql: err
On Wed, Nov 22, 2023 at 5:09 PM Laurenz Albe
wrote:
>
> One of the problems is what should be included.
> Indexes? Policies? Constraints?
>
A high limit could be all objects except data.
All the objects which would be deleted by a 'DROP TABLE'.
Maybe including 'CASCADE'?
No unsurmountable qu
On Wed, Dec 6, 2023 at 3:39 PM roger popa wrote:
> You can tell if exists an oficial Postgresql Certification issued by
> postgresql.org ?
>
No.
> Or others (like PearsonVue)?
>
EnterpriseDB has certifications. I think you can get some of them for free.
https://www.enterprisedb.com/accounts/r
Hi
Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful
with a pg_get_tabledef() to get a full description of how a table is
defined.
Currently the table definition can be extracted with the command:
pg_dump -d foo --schema-only --table=bar | egrep '^[^-]'
The psql command
---
PostgreSQL 14beta1, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)
Thanks for investigating
Hans Buschmann
el_setup_cost = 25000
Thanks
Hans Buschmann
idth=4) (actual time=6.864..7.764
rows=1 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003
rows=1 loops=1)
Planning Time: 0.026 ms
Execution Time: 7.812 ms
(7 rows)
Hans Buschmann
nux.
There seem no relevant GUC differences concerning query execution, so the
performance penalty of 300% to 900% (one step only) is not easily explainable.
The databases remain on the system to repeat the queries on request in the
queue of further investigation.
Thanks for looking.
Hans Buschm
you trigger can be much faster if you replace FOREACH cycle by unnest
INSERT INTO generic.usergroup_test(test_id, usergroup_id) VALUES(NEW.id,
UNNEST(NEW.usergroup_ids));
Thanks! Appreciated :-)
Hans
of an extra table. Having real arrays
of foreign keys would be nice, but this works too).
Hans
enabled if that helps in understanding this problem. What would be
helpful in troubleshooting this?
Thanks in advance for any insight you may have,
Hans
On Fri, Sep 13, 2019 at 4:14 PM stan wrote:
> Is there a way to "export" a single table, that can be easily re
> "imported"?
>
Export:
pg_dump --table=foo > foo.sql
Import:
cat foo.sql | psql
>
>
> > I've updated word_similarity_op(text,text) to be leakproof, and
> > pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to
> > <%, though I haven't found explicit confirmation. However, using
> > word_similarity() instead of <% on a 100k row table, without any RLS
> > invo
k row table, without any RLS
involved, doesn't make use of the index, while using <% does. Obviously,
adding the RLS doesn't make that any better. Any idea what might be the
cause?
On Tue, Aug 13, 2019 at 5:39 PM Stephen Frost wrote:
> Greetings,
>
> * Derek Hans (derek.h..
Thanks for the pointer for marking functions as leakproof, I was unaware of
that whole concept.
Unfortunately only "alter function" supports "leakproof" - "alter operator"
does not. Is there a function-equivalent for marking operators as
leakproof? Is there any documentation for which operators/fu
>
>
> Your example is obscuring the issue by incorporating a tenant_name
> condition (where did that come from, anyway?) in one case and not
> the other. Without knowing how selective that is, it's hard to
> compare the EXPLAIN results.
>
>
That's RLS kicking in - RLS condition is defined as
((ten
>
>
> What are the RLS policies on the table?
>
> From select * from pg_policies:
"((tenant_name)::name = CURRENT_USER)"
> What is the definition of the GIN index?
>
> CREATE INDEX search__gist
ON public.search USING gist
(search COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE
When using row level security, GIN and GIST indexes appear to get ignored.
Is this expected behavior? Can I change the query to get PostgreSQL using
the index? For example, with RLS enabled, this query:
select * from search where search like '%yo'
Creates this query plan:
"Seq Scan on search (co
Try run postgresqltuner.pl as suggested on
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and also
look at the other info there.
After running a few days with live data run cache_hit_ratio.sql by Melvin
Davidson:
SELECT pg_stat_database.datname,
pg_stat_database.blks_read,
On Sun, Mar 17, 2019 at 11:54 PM Rory Campbell-Lange <
r...@campbell-lange.net> wrote:
> We aren't sure whether to use software MDRaid or a MegaRAID card.
>
Never go with hardRaid. I have had a breakdown on a hardware RAID and as
it was special and not off-the-shelf, I could not move the disk to
On Sat, Mar 23, 2019 at 3:48 PM Igor Korot wrote:
>
> You mean even running as "sudo"?
>
igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2
| tar xpf -
No, you are not running tar as sudo.
I would at least suggest:
cd /usr/local/src
sudo tar --bzip2 xf /usr/postgresql-9
2, Derek Hans wrote:
>
> > I've set up 2 instances of PostgreSQL 11. On instance A, I created a
> table
> > with 2 local partitions and 2 partitions on instance B using foreign data
> > wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> > Inser
s!
On Fri, Feb 22, 2019 at 9:44 AM Derek Hans wrote:
> I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
> with 2 local partitions and 2 partitions on instance B using foreign data
> wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
&g
I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
with 2 local partitions and 2 partitions on instance B using foreign data
wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
Inserting rows into this table works as expected, with rows ending up in
the app
there is no Windows OS.
https://en.wikipedia.org/wiki/Compatibility_layer
That said, WSL is a great tool for developers. Better than Cygwin.
./hans
When one get a "No space left on device" and there is a lot of space it is
sometimes caused by lack of inodes.
Try run the command:
df --inodes
On Mon, Dec 10, 2018 at 4:56 PM Ron wrote:
> Hi,
>
> v9.6.6
>
>
> 2018-12-07 06:21:55.504 EST 10.140.181.89(35868) CDS CDSLBXW 13748 SELECT
> Postgr
Are you sure that swap is used actively? Maybe it had just been used during
backup or something.
Look after SwapIn/SwapOut (si/so) it should be '0'
$ vmstat 1
procs ---memory-- ---swap-- -io -system--
--cpu-
r b swpd free buff cache si sobibo
On Wed, Jul 18, 2018 at 10:44 AM Andreas Kretschmer
wrote:
>
> ||pg_stat_reset()
>
Thanks, I guess we can see the result in a few days.
BTW, strang command: it only reset current database and it can't take db as
parameter.
Hi
I have this system with some databases and I have run the
cache_hit_ratio.sql script on it. It showed that the db acme777booking had
a ratio on 85%. I then changed shared_buffer size from 0.5GB to 4GB as the
server has 16GB of physical RAM. After 6 days of running I checked the
ratio again and
it will be fast.
Note: NULL should be '\N', see manual:
https://www.postgresql.org/docs/current/static/sql-copy.html
It is the same kind of data you get with pg_dump.
./hans
"FATAL: remaining
connection slots are reserved for non-replication superuser connections"
Thanks,
-hans
Hi
Can I have a advise on how to handle groups?
In my Windows AD (Active Directory) I have two groups named:
readers
writers
In Postgresql I have these databases:
d1
d2
The "writers" should have NOCREATEDB etc but ALL PRIVILEGES to d1 and d2.
The "readers" should have SELECT to all tab
On Thu, Mar 15, 2018 at 12:44 AM, Tom Lane wrote:
>
> The query does fail on < 9.2, because on rows with no reltoastrelid
>
Thats, fine. I will live with that until upgrade.
> But hey, it's a wiki;
> if you feel more ambitious, edit away.
>
I tried but it said:
"The site you are trying to log
On Thu, Mar 15, 2018 at 12:32 AM, Michael Paquier
wrote:
> > I'm running version 9.1.9 so it should be working according to the
> > wiki.
>
> You should update and upgrade. 9.1 has fallen out of community support
>
I will recommend that to the database owner. Thanks
Hi
I got the message
ERROR: could not open relation with OID 0
when running the "General Table Size Information" from
https://wiki.postgresql.org/wiki/Disk_Usage
This patch gives some system tables
@@ -12,5 +12,6 @@
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.rel
2017-12-21 21:50 GMT+01:00 Rakesh Kumar :
> whenever SQLERROR EXIT ROLLBACK
>
Thanks. You are absolutely right.
After starting with:
WHENEVER SQLERROR EXIT ROLLBACK
the process getting the deadlock will exit to command prompt (with
%ERRORLEVEL% = 0).
So what actually found out was that Oracle
2017-12-21 12:41 GMT+01:00 Rakesh Kumar :
> Could it be that the tool you are using in Oracle is doing commit while
> exiting out due to Deadlock, because there is no explicit rollback.
>
The tool Im using is "sqlplus". By default you are always in a transaction
and auto-commit only occur on exit
2017-12-21 17:46 GMT+01:00 Jeremy Finzel :
> It's hard to follow how the 2 videos relate, because you don't run the
> same SQL both places. You first update where i = 2 in Postgres and i = 1
> in Oracle.
>
Well OK. I made a new one for PostgreSQL: https://youtu.be/En8EFv90yCc
Now with same backg
tgreSQL:
A
select * from t;
begin;
update t set n=n+1 where i=2;
B
begin;
update t set n=n+1 where i=1;
update t set n=n+1 where i=2;
A
update t set n=n+1 where i=1;
B
commit;
A
commit;
best regards
hans
44 matches
Mail list logo
