On 9/18/19 10:18 PM, Jerry Sievers wrote:
> David Steele writes:
>
>> This is not an issue unless you seriously game the system. When a
>
> And/or your recovery system is running archive_mode=always :-)
>
> I don't know how popular that setting value is
Hi - I’m working on a database that will be accessed via a web app. I’m
used to a setup where there is one account/role that the app would use to
access the database, but in my current scenario I’m interested in row level
security and auditing enforced by the database. In this scenario, would it
ma
Perhaps someone can guide me here as I'm having a "moment". :)
Not sure why I am getting 0 rows returned here:
db=# \d table_name_ds_tmp
Column | Type| Modifiers
+---+---
categoryid | bigint|
name | character varying |
d
Thanks,
Unfortunately I believe I need to include a postgres module to get the "<@"
operator, which I have no power to do. This is what I get with that
operator..
select name from table_name_ds_tmp where categoryid <@ ANY ( ARRAY[ 200,
400]::BIGINT[] );
ERROR: operator does not exist: bigint
sion)
>
> which compares "value" to each element of the array value (which had
> better have elements of a type comparable to "value"). What you
> can't do is generate the array value from a sub-select, because that
> will be taken as being an instance of the first feature.
>
> David didn't say what his real problem was, but I'd suggest that
> making his sub-select return a rowset result rather than an array
> result might be the best way to resolve things. It's more SQL-y,
> for sure.
>
> regards, tom lane
>
would be more than just executing the query without any
partition pruning. I don't see any good way to know in advance if it
would be worthwhile or not.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Hi:
psql (9.6.7, server 11.3) on linux
What are the possibilities regarding restricting user access to records
given this scenario.
I have a DB with tables that are organized in a hierarchical way. For
example, a "projects" table is the parent of >1 recs in a "domains" table
(PK/FK setup), whic
Ok, thanks.
I was hoping there was a way to integrate the user/permissions/groups in
linux with the PG permissions functionality.
On Tue, Oct 15, 2019 at 12:32 PM Michael Lewis wrote:
> It sounds like you want row level security-
> https://www.postgresql.org/docs/9.6/ddl-rowsecurity.html
>
> But
ble standby for that one backup.
Regards,
--
-David
da...@pgmasters.net
ta/11/recovery.conf
INFO: restore global/pg_control (performed last
to ensure aborted restores cannot be started)
INFO: restore command end: completed successfully (5113ms)
pgBackRest also has a tutorial on PITR:
https://pgbackrest.org/user-guide.html#pitr
--
-David
da...@pgmasters.net
ill the end of the current transaction"
Neither affects other sessions.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
uot;sub-partitioning". If
you're still uncertain after that, then ask here.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
#upgrade-stanza.
Regards,
--
-David
da...@pgmasters.net
3h), so I think the next step
is to try to identify the connection at the OS level with netstat to see what
state it's in.
Thanks for your help.
Regards,
David
On 2/12/19, 11:17 pm, "Tom Lane" wrote:
David Wheeler writes:
> We have a query that our system r
string here to an
unsigned int...
That's exactly what should be happening. There has already been a bug
filed at https://github.com/pgbackrest/pgbackrest/issues/910 and you can
find more details there.
Regards,
--
-David
da...@pgmasters.net
On 1/21/20 8:45 AM, David Steele wrote:
On 1/21/20 12:55 AM, Eric Veldhuyzen wrote:
Ron wrote:
On 1/21/20 1:10 AM, Eric Veldhuyzen wrote:
Hi,
We are using pgbackrest (2.21) to backup out postgresql (11) clusters.
Last night our nightly diff backup gave me the
ERROR: unable to convert base 10
Hi:
How does one reformat the output of the "age" function to always be in
terms of hours:mins.
E.g.
dvdb=> select age('2020-03-05 01:40:32-05','2020-03-01 21:56:05-05');
age
-
3 days 03:44:27
(1 row)
I want...
"75:44"
I'm not married to "age" If there's a better way
On Tue, 10 Mar 2020 at 02:16, Sterpu Victor wrote:
> I'm testing on Postgresql 12.1 and I have a index like this:
> "check_dates_gist" EXCLUDE USING gist (id_test1 WITH =, id_test2 WITH =,
> tsrange(valid_from::timestamp without time zone, valid_to::timestamp without
> time zone) WITH &&)
>
> Wh
Hi:
psql (9.6.7, server 11.3) (linux)
Is there a way to set all constraints on all tables of a DB and/or schema
to be deferrable ? Or do I have to do them one-by-one ?
Here's an interesting one for you...
psql (9.6.7, server 11.3) on linux
I have 2 DBs, differnet servers/instances. I want to take all the metadata
and data for a set of tables/views in the public schema of one DB and move
it all over to be inside a schema of a second DB/instance.
I'm using pg_du
th "myschem." in the
output of pg_dump, maybe with sed or something. But even after explicitly
using "public.", it didn't stick in the view def.
On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver
wrote:
> On 3/26/20 10:16 AM, David Gauthier wrote:
> > Here's an intere
ep_events fse
WHERE fse.sqf_id = sr.sqf_id));
sqf=>
You can see the "public." refs in the create view, but not echoed in the
stored view def.
On Thu, Mar 26, 2020 at 1:58 PM Adrian Klaver
wrote:
> On 3/26/20 10:55 AM, David Gauthier wrote:
> > Thanks Adrian for th
psql (9.6.7, server 11.3) on linux
I want to do something like this
(intentionally bad sql but will illustrate the need)
select s.name,s.grade from students s where s.class='math'
union
select 'whole class', class_grade from all_classes where class=s.class
Of course it's that "where class=s.clas
uld be with
archive_mode=off.
What are you trying to accomplish?
Regards,
--
-David
da...@pgmasters.net
psql (9.6.0, server 11.3) on linux
We've ramped up usage on a PG server (a VM, I have no choice about this)
and are approaching the 100 connections limit. We could increase the
limit, but I've read that this can lead to a degradation in performance. If
we bump it up to 500, what kind of compute r
After looking at some of the factors that can affect this, I think it may
be important to know that most of the connections will be almost idle (in
terms of interacting with the DB). The "users" are perl/dbi scripts which
connect to the DB and spend the vast majority of the time doing things
other
psql (9.6.7, server 11.3) on linux
In the copy/paste below, first 2 lines returned by a select on the view,
why didn't it sort on start_datetime correctly ? I would think that the
one started on 04-08 would come before the one on 04-09 ?
dvdb=> \d sim_phases;
Table "dvm.sim_phase
On Fri, 17 Apr 2020 at 20:13, Kiwoong Choi wrote:
> Is there a script and sample data to test the performance of postgresql?
There is pgbench. https://www.postgresql.org/docs/current/pgbench.html
David
Hi:
psql (9.6.7, server 11.3) on linux
I have what appear to be a log of idle connections to my DB. Query of
pg_stat_activity indicates well over half (127/206) are like this...
dvdb=# select state_change,wait_event_type,wait_event,state,backend_type
from pg_stat_activity where query = '';
Thanks!
And an example of connection pooling is pgBouncer ?
On Thu, Apr 23, 2020 at 2:41 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thursday, April 23, 2020, David Gauthier
> wrote:
>
>> Hi:
>>
>> psql (9.6.7, server 11.3) on linux
>&g
psql (9.6.7, server 11.3)
I have a table...
dvdb=# \d+ dvm_events;
Table "dvm.dvm_events"
Column | Type | Modifiers | Storage | Stats
target | Description
--+--+---+-
such mechanism
Currently there is no command to verify a backup without restoring it.
However, we are planning a verify command in the near future -- most
likely within the next few releases.
Regards,
--
-David
da...@pgmasters.net
However, pgbench does TRUNCATE the tables before doing
the COPY, so that'll be most likely what's causing your query to get
blocked.
David
s/blob/REL_11_6/src/bin/pgbench/pgbench.c#L3599
David
On Fri, 8 May 2020 at 10:00, David G. Johnston
wrote:
>
> On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote:
>>
>> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS
>> pa_process_activity_id FROM process_activity pa WHERE pa.app_id =
n UPDATE/DELETE as auto-vacuum, in 9.6, does not visit those
tables for any other reason. After the first time, the relfrozenxids
of tables tend to be more staggered so their vacuum freeze
requirements are also more staggered and that tends to cause fewer
problems.
David
t; production system ?
Yes, but you cannot increase the per-table setting above the global
setting. Changing the global setting requires a restart.
David
psql (9.6.0, server 11.3) on linux
Looking for ideas. I want a trigger to...
1) compose an html report based on DB content
2) email the report to a dist list (dl = value of a table column)
If this will involve hybrid coding, I prefer PL/perl. The linux env has
both "mail" and "mutt" (if this is
Got it.
On Fri, May 8, 2020 at 2:05 PM David G. Johnston
wrote:
> On Fri, May 8, 2020 at 10:19 AM Christophe Pettus
> wrote:
>
>> If you don't want to periodically poll the table, you can use NOTIFY
>> within the trigger to wake up a process that is waiting on NOTIF
There is also What3Words.com, which give a three word name to each 3m square
over the
world. Longer that USNG but easier to remember/type/say.
David
On Wednesday, 13 May 2020 14:33:30 BST Basques, Bob (CI-StPaul) wrote:
> I've been following this thread with some interest.
>
>
f using ROW_NUMBER(). That might be a bit more efficient, but
it's unlikely to matter too much since there are only 167 rows in that
table.
David
On Tue, 26 May 2020 at 22:31, Frank Millman wrote:
> Thank you David. I tried that and it produced the correct result in
> 53ms, which is what I am looking for.
>
> It will take me some time to understand it fully, so I have some
> homework to do!
The main problem with your pre
On Tue, 26 May 2020 at 23:41, Vik Fearing wrote:
>
> On 5/26/20 12:04 PM, David Rowley wrote:
> > Since "my_table" is small, you'd likely be much better doing a manual
> > rewrite of the query to join a subquery containing the required
> > details from
psql (9.6.0, server 11.3) linux
Hi:
I'm a PG users who has asked our IT team to install pl/perlu on an existing
9.6.0 instance on linux. They really don't know how to approach this.
Could someone point me to a good step-by-step (including ptrs to any
downloads they may need) ?
Also, when they d
something simple as a novice who's
looking into getting into working on Postgres might skim that list for
something to work on. More experienced people, I imagine, would never
look there. FWIW, many people who are now working on PostgreSQL once
came along with a question or idea like yours. Many have been unable
to escape ever since :)
David
[1] https://wiki.postgresql.org/wiki/Todo
e
have various other optimisations when multiple subcommands are used in
a single ALTER TABLE. However, I'm not suggesting we go and allow
indexes to be created in ALTER TABLE. I"m just suggesting that it
would likely be a better alternative than inventing something to track
when a table last had
David
g the hash table
fit better into a CPU cache. For merge joins, sorts, having the data
partially pre-sorted in chunks means fewer operations for qsort which
can result in speedups.
David
create a unique constraint. If you
create a unique constraint, it'll create a unique index to enforce the
constraint. ON CONSTRAINT requires a constraint name, not an index
name.
David
ditional statistics you created? Why was it on only
> two columns? Did you include MCVs type of extended stats?
Unfortunately, the join selectivity functions have yet to learn about
extended statistics.
David
timation is
being worked on [1], so there's a chance that it may happen for PG14.
David
[1] https://commitfest.postgresql.org/28/2386/
u select which partition a
varchar value should go into if you didn't use a hash function.
David
we see that.
For your version, you might want to look at
https://wiki.postgresql.org/wiki/Loose_indexscan and try some of the
proposed solutions from there.
David
Hi:
9.6.0 on linux
I have a table which logs the inception of an event with an
"incept_datetime" (timestamptz) field. I want to know how many events
occurred per day from one date to the next using midnight as a marker for
each day reported. The end result should be something like...
2019-06-23
e, but...
I guess you could work around this problem by just not putting the
midnight time in your timestamp. However, that might not work so well
if you want to specify a time other than midnight.
David
9.6.7 on linux
This query, which has 2 outer joins, gives me the records that I want...
dvdb=# select
dvdb-# sqf.sqf_runs.sqf_id,
dvdb-# sqf.sqf_runs.submitted_shelvelist as sqf_sl,
dvdb-# dvm.workarea_env.p4_changelist as as_cl,
dvdb-# dvm.workarea_env.wa_id,
dvdb-# dvm.dvm_events
se of the outer join)
On Mon, Jun 29, 2020 at 9:10 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, June 29, 2020, David Gauthier wrote:
>
>>
>>sqf_id
ization.
>
> First query returns lots of random rows, the second query returns just one
> random row.
I think the idea here is that because the target list contains only
constants that pulling additional rows from the query after the first
one will just be a duplicate row and never add any rows after the
UNION is processed.
David
On Tue, 7 Jul 2020 at 09:03, Simon Riggs wrote:
>
> On Mon, 6 Jul 2020 at 21:49, David Rowley wrote:
>>
>> On Tue, 7 Jul 2020 at 00:43, Simon Riggs wrote:
>> >
>> > On Mon, 6 Jul 2020 at 12:37, Robins Tharakan wrote:
>> >> (SELECT 1 FROM pg
On Sat, 11 Jul 2020 at 02:48, Vishwa Kalyankar
wrote:
> Below is the output of the query explain and analyze result.
You may find better help here if you follow the advice given in
https://wiki.postgresql.org/wiki/Slow_Query_Questions
David
t1 where not exists(select 1 from t2
where t1.a=t2.a);
update_t1 | a
---+---
2 | 1
(1 row)
If you're happy that you don't have any functions like that which
could momentarily cause the foreign key to appear violated, then there
shouldn't be any harm in changing the INNER JOIN on users to a LEFT
JOIN. PostgreSQL will be able to remove the join in that case.
David
putting the volatile function in a
sub-select with no FROM clause.
SELECT ... FROM ... WHERE id = (SELECT ceiling(random()*2582));
Or the more traditional way to get a random row is:
SELECT ... FROM ... WHERE id BETWEEN 0 AND 2585 ORDER BY random() LIMIT 1;
David
tables and indexes referenced in the
query. If the query touches views or custom functions, we'll need
those definitions as well. Run psql command "\d table" with the
tables/views/indices referenced in the problem query."
David
if pg_class.relpages == 0. So even if you were to manually
vacuum the table the code here would think it's not yet been vacuumed.
David
r the 999 partitions.
However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.
David
On Sat, 25 Jul 2020 at 10:37, Tom Lane wrote:
>
> David Rowley writes:
> > However, for now, you might just want to try raising various jit
> > thresholds so that it only is enabled for more expensive plans.
>
> Yeah. I'm fairly convinced that the v12 defaults are
On Sat, 25 Jul 2020 at 10:42, David Rowley wrote:
>
> On Sat, 25 Jul 2020 at 10:37, Tom Lane wrote:
> >
> > David Rowley writes:
> > > However, for now, you might just want to try raising various jit
> > > thresholds so that it only is enabled for more expens
On Sun, 26 Jul 2020 at 02:17, Tom Lane wrote:
>
> David Rowley writes:
> > On Sat, 25 Jul 2020 at 10:42, David Rowley wrote:
> >> I think plan cost overestimation is a common cause of unwanted jit too.
> >> It would be good to see the EXPLAIN ANALYZE so we knew
es of
assuming, say 1000 * cpu_operator_cost to compile an Expr then assume
that a compiled Expr will be some percentage faster than an evaluated
one and only jit when the Expr is likely to be evaluated enough times
for it to be an overall win. Optimize and inline would just have
higher thresholds.
David
On Sun, 26 Jul 2020 at 02:54, Tom Lane wrote:
>
> David Rowley writes:
> > ... nested at the bottom level join, about 6 joins deep. The lack of
> > any row being found results in upper level joins not having to do
> > anything, and the majority of the plan is (never exec
On Tue, 28 Jul 2020 at 11:00, Andres Freund wrote:
>
> On 2020-07-25 10:54:18 -0400, Tom Lane wrote:
> > David Rowley writes:
> > > ... nested at the bottom level join, about 6 joins deep. The lack of
> > > any row being found results in upper level joins not ha
JIT compilation available in this session (see Chapter 31)?
Returns false if jit is set to false."
Maybe this would be better?
"returns true if jit is enabled and JIT compilation is available in
this session (see Chapter 31)."
Open to other suggestions.
David
On Tue, 28 Jul 2020 at 15:33, Tom Lane wrote:
>
> David Rowley writes:
> > Maybe this would be better?
>
> > "returns true if jit is enabled and JIT compilation is available in
> > this session (see Chapter 31)."
>
> The general, non-hacker meaning of
On Tue, 28 Jul 2020 at 15:55, David Rowley wrote:
>
> On Tue, 28 Jul 2020 at 15:33, Tom Lane wrote:
> >
> > David Rowley writes:
> > > Maybe this would be better?
> >
> > > "returns true if jit is enabled and JIT compilation is available in
&g
Hi:
I need a free odbc driver for PG to be installed on Windows 10 that my user
community can easily install. By "easily install" I mean no binaries, no
zipped file, etc... just point-click-install (with the usual confirmations
and accepting default destinations for the code and such).
Devart us
(-8)? Which should I use?
It did recently change from -8 to -7 when we removed Oid as a system
column in pg12. The number will never change on a major version, so
you'll always know what it is for versions that have already been
released. There's always a chance it'll change from -7 in some future
PostgreSQL version though.
David
compiler extension is available (see Chapter
31) and the jit configuration parameter is set to on."
David
not have changed that since branching for PG13.
Perhaps you've got some tablespace level random_page_cost set and
you're not actually changing it?
David
On Wed, 29 Jul 2020 at 09:07, Andres Freund wrote:
> On 2020-07-28 11:54:53 +1200, David Rowley wrote:
> > Is there some reason that we can't consider jitting on a more granular
> > basis?
>
> There's a substantial "constant" overhead of doing JIT. And
.
You can use MyDatabaseId in miscadmin.h
If you git grep MyDatabaseId in src/contrib you can see some examples.
A good tip for the future would be to just look at the source code of
the built-in function and see how it does it.
David
nt to go to too
much trouble unless this is seen as a good direction to go in.
> That obviously needs to be improved further, but it's already a lot
> better. In particular after these changes the generated code could be
> cached.
That would be a game-changer.
David
ows exist
that need that value. Rows that are added with no value specified for
the license column will just have the DEFAULT value, which is the one
stored in pg_attrdef.
David
Hi:
version 9.6 on linux
Our IT dept configured our DB to be "High Availability" a couple months
back. I believe this means there's a backup server and disks that mirror
the main and can kick in should main go down.
They need to install a patch on the servers which will require server
downtime.
Our IT dept needs to install a patch on both primary and backup servers for
our Postgres Automatic Failover configured DB (version 9.6 on linux). From
the standpoint of the DB users, can a strategy be implemented such that
they see zero downtime during this process as the 2 servers are taken down
Hi:
Our IT dept has created what they call a High Availability DB for our PG DB
(9.6.7 on linux). If the primary fails, they promise to promote the backup
to be the new primary but leave it at that. But from the perspective of
the app, I'm left with
1) detecting an SQL error is a DB connecti
is orchestrating the HA cfg.
On Tue, Aug 11, 2020 at 11:46 AM Paul Förster
wrote:
> Hi David,
>
> > On 11. Aug, 2020, at 17:12, David Gauthier
> wrote:
> >
> > Hi:
> >
> > Our IT dept has created what they call a High Availability DB for our PG
> DB (9.
e event of a primary/backup swap.
-dave
On Wed, Aug 12, 2020 at 4:10 AM Paul Förster
wrote:
> Hi David,
>
> please don't top-post.
>
> > On 11. Aug, 2020, at 22:57, David Gauthier
> wrote:
> >
> > Thanks for the response Paul :-)
> >
> > Our code
9.6.7 on linux
I need to insert the linux username of a user on the client side into a col
using an insert statement. I realize that the server knows nothing about
who the linux user was on a client, but I was thinking that I might be able
to pass that in somehow through a variable.
Looking at
approach, but don't really know where/how to
create it in terms of pg sys files, init scripts or env vars like
PGOPTIONS.
On Mon, Aug 17, 2020 at 4:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Aug 17, 2020 at 12:53 PM David Gauthier
> wrote:
>
>&
t to pass a variable like
this to the server which it then could read on the server side?
On Mon, Aug 17, 2020 at 5:53 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, August 17, 2020, David Gauthier
> wrote:
>
>> OK, trying to piece together something
I can recommend sqlx:
https://github.com/jmoiron/sqlx
On Tue, Aug 18, 2020 at 4:56 PM Scottix wrote:
> I have been using pgx and seems to be working well.
>
> I recommend if your just starting to use the v4 version.
>
> On Tue, Aug 18, 2020 at 5:53 AM Stephen Frost wrote:
>
>> Greetings,
>>
>>
s://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/costsize.c#L5699
, if you hunt around for usages of that function then you'll see the
estimated row counts are divided by the return value of that function.
David
On Fri, 21 Aug 2020 at 03:21, Philip Semanchuk
wrote:
>
>
>
> > On Aug 19, 2020, at 6:24 PM, David Rowley wrote:
> >
> > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk
> > wrote:
> >> -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67
r idea.
If ANALYZE is not getting you a good value for n_distinct, then you
can overwrite it. See [1], search for n_distinct.
David
[1] https://www.postgresql.org/docs/current/sql-altertable.html
egative value as mentioned in the doc link.
David
the parallel_tuple_cost a
bit, which might give you a Gather, so at least the Seq Scan will be
done in parallel. The sort will still be serial though.
A btree index on table1 (c) looks like it might be worth considering.
David
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e89a71fb44
psql (11.5, server 11.3) on linux
I'm using MS-Access as a Windows front-end to a PG DB table through ODBC
(PostgreSQL Unicode ODBC Driver). Seems to be working fine except for when
users enter "..." as part of a string, MS (in it's infinite wisdom) decides
to translate that to what emacs is desc
Next question, how does one actually replace the thing ?
replace(thestring,0x2026,'...')
... isn't going to fly.
Working with binary values in PG isn't at the top of my resume :-)
On Fri, Oct 30, 2020 at 12:20 PM Tim Clarke wrote:
>
> On 30/10/2020 16:03, David Gaut
n-time
pruning, so it makes sense to allow run-time pruning on those to work
going forward.
Thanks for the report.
David
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a929e17e5a8c9b751b66002c8a89fdebdacfe194
I Could not read your config file but
What is the size of the Postgres log file?
Do you have a log rotation policy on it?
Perhaps your Postgres log level is to high or your connections are
generating a lot of errors that need investigating.
Dave
On Wed, Nov 4, 2020 at 5:24 AM Shani Israeli
wrote
Hi:
version 11.5 on linux.
Our IT dept has configured our PG DB as a "High Availability" database. It
has a primary and backup server (disks too). Normally both are running but
if one goes down, the other is still available for use, effectively keeping
the DB up while the failed server is being
101 - 200 of 1917 matches
Mail list logo
