Re: Same query, same data different plan

[Daevor The Devoted]

Hi

Is the table stats up to date on both?

https://www.postgresql.org/docs/current/planner-stats.html

Best regards,
Na-iem Dollie

On Mon, Oct 10, 2022 at 2:56 PM Kostas Papadopoulos <
kos...@methodosit.com.cy> wrote:

>
> I have two identical databases running in the same instance of Postgresql.
> Ran
> analyze on both. Running the same query I'm getting different plans, one
> x10 slower.
> Although I have solved my problem by re-writing the query, I want to
> understand why
> this is happening. If the configuration, Postgresql version, schema and
> data are the
> same, what other factors is the planner considering?
>
> --
>
> Kostas Papadopoulos
> KE MethodosIT
>
>
>
>

Re: 20220221-Clarification regarding PostgeSQL DB backup

[Daevor The Devoted]

Hi Karthick

In that case, your are options are:

1. Continuous WAL Archiving
<https://www.postgresql.org/docs/current/continuous-archiving.html>
This is "like" using diff backups, but not quite the same.

OR

2. Use a 3rd party app, for example Barman <https://pgbarman.org/> or
pgBackRest <https://pgbackrest.org/>

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 8:25 AM Techsupport  wrote:

> *Hi ~~Na-iem Dollie,*
>
>
>
> Thanks  for your reply,
>
>
>
> In SQL Server there is an option to take Full backup and Differential
> Backup for a particular database.
>
>
>
> Like that, we need to take the differential Backup in PostgreSQL. This is
> my primary need.
>
>
>
> *Thanks,*
>
> *Karthick Ramu*
>
>
>
>
>
> *From:* Daevor The Devoted [mailto:doll...@gmail.com]
> *Sent:* Monday, February 21, 2022 11:05 AM
> *To:* Techsupport
> *Cc:* pgsql-generallists.postgresql.org
> *Subject:* Re: 20220221-Clarification regarding PostgeSQL DB backup
>
>
>
> Hi Karthick
>
>
>
> I'm a little rusty on PG (it's been about a decade since I last worked
> intensely with it), but I seem to remember that restoring with indexes can
> be very slow. The faster approach is to restore *without* indexes, and
> then create the indexes once the data restore is complete.
>
>
>
> My knowledge may be outdated, so best to check with others that have more
> recent knowledge. However, it's probably worth investigating in the
> meantime.
>
>
>
> Okay, I just had a quick look at the documentation, and it seems there is
> an option to run multiple concurrent jobs for the time-consuming parts of a
> restore (which includes index creation):
>
>
>
> -j *number-of-jobs*
>
>
>
> see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check
> the docs for whatever PG version you have)
>
>
>
> Good luck!
>
>
>
> ~~Na-iem Dollie
>
>
>
> On Mon, Feb 21, 2022 at 6:59 AM Techsupport 
> wrote:
>
> Hi Team,
>
>
>
> We have used PG_DUMP to take backup of particular database from the
> PostgreSQL Server. It takes too long to restore the databases , which has
>  Half Billion records (almost 8 Hour)
>
>
>
> My primary need is to make the Differential and Incremental backup on the
> Windows Server. When I search there is a tool Barman and PG_BackRest is
> available. But that will be supported only for Linux based servers only
>
>
>
> Please suggest me a tool to make differential backup
>
>
>
> Thanks,
>
> Karthick Ramu
>
>

Re: 20220221-Clarification regarding PostgeSQL DB backup

[Daevor The Devoted]

Hi Karthick

I'm a little rusty on PG (it's been about a decade since I last worked
intensely with it), but I seem to remember that restoring with indexes can
be very slow. The faster approach is to restore *without* indexes, and then
create the indexes once the data restore is complete.

My knowledge may be outdated, so best to check with others that have more
recent knowledge. However, it's probably worth investigating in the
meantime.

Okay, I just had a quick look at the documentation, and it seems there is
an option to run multiple concurrent jobs for the time-consuming parts of a
restore (which includes index creation):

-j *number-of-jobs*

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check
the docs for whatever PG version you have)

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 6:59 AM Techsupport  wrote:

> Hi Team,
>
>
>
> We have used PG_DUMP to take backup of particular database from the
> PostgreSQL Server. It takes too long to restore the databases , which has
>  Half Billion records (almost 8 Hour)
>
>
>
> My primary need is to make the Differential and Incremental backup on the
> Windows Server. When I search there is a tool Barman and PG_BackRest is
> available. But that will be supported only for Linux based servers only
>
>
>
> Please suggest me a tool to make differential backup
>
>
>
> Thanks,
>
> Karthick Ramu
>

Re: POLL: Adding transaction status to default psql prompt

[Daevor The Devoted]

+1

(+10 billion actually, but sadly, I'm only allowed a +1)

On Thu, Feb 6, 2020 at 4:55 AM Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> 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.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower <gavinflo...@archidevsys.co.nz
> wrote:

> On 02/03/18 06:47, Daevor The Devoted wrote:
>
>>
>> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar...@aol.com
>> <mailto:rakeshkumar...@aol.com>> wrote:
>>
>>
>> >Adding a surrogate key to such a table just adds overhead,
>> although that could be useful
>> >in case specific rows need updating or deleting without also
>> modifying the other rows with
>> >that same data - normally, only insertions and selections happen
>> on such tables though,
>> >and updates or deletes are absolutely forbidden - corrections
>> happen by inserting rows with
>> >an opposite transaction.
>>
>> I routinely add surrogate keys like serial col to a table already
>> having a nice candidate keys
>> to make it easy to join tables.  SQL starts looking ungainly when
>> you have a 3 col primary
>> key and need to join it with child tables.
>>
>>
>> I was always of the opinion that a mandatory surrogate key (as you
>> describe) is good practice.
>> Sure there may be a unique key according to business logic (which may be
>> consist of those "ungainly" multiple columns), but guess what, business
>> logic changes, and then you're screwed! So using a primary key whose sole
>> purpose is to be a primary key makes perfect sense to me.
>>
>
> I once worked in a data base that had primary keys of at least 4 columns,
> all character fields, Primary Key could easily exceed 45 characters.
> Parent child structure was at least 4 deep.
>
> A child table only needs to know its parent, so there is no logical need
> to include its parent and higher tables primary keys, and then have to add
> a field to make the composite primary key unique!  So if every table has
> int (or long) primary keys, then a child only need a single field to
> reference its parent.
>
> Some apparently safe Natural Keys might change unexpectedly.  A few years
> aback there was a long thread on Natural versus Surrogate keys - plenty of
> examples were using Natural Keys can give grief when they had to be
> changed!  I think it best to isolate a database from external changes as
> much as is practicable.
>
> Surrogate keys also simply coding, be it in SQL or Java, or whatever
> language is flavour of the month.  Also it makes setting up testdata and
> debugging easier.
>
> I almost invariably define a Surrogate key when I design tables.
>
>
> Cheers,
> Gavin
>
>
> Thank you! I think you have expressed far more clearly what I have been
trying to say. +10 to you.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Thu, Mar 1, 2018 at 10:36 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Mar 1, 2018 at 1:32 PM, marcelo  wrote:
>
>>  What´s the question?
>>
>>
> ​Whether the OP, who hasn't come back, knew they were starting a flame war
> by asking this question...
>
> There is no context-less "right place" to place validation logic, nor are
> the various options mutually exclusive.
>
> David J.
> ​
>

This I can wholeheartedly agree with. And my apologies if I came across as
"flaming". Not my intention at all. I'm simply here to learn (and, well,
offer my opinion from time to time :) )

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson  wrote:
>
>> Why have the overhead of a second unique index?  If it's "ease of joins",
>> then I agree with Francisco Olarte and use the business logic keys in your
>> joins even though it's a bit of extra work.
>>
>
> ​The strongest case, for me, when a surrogate key is highly desirable is
> when there is no truly natural key and the best key for the model is
> potentially alterable.  Specific, the "name" of something.  If I add myself
> to a database and make name unique, so David Johnston, then someone else
> comes along with the same name and now I want to add the new person as, say
> David A. Johnston AND rename my existing record to David G. Johnston.  I
> keep the needed uniqueness ​and don't need to cobble together other data
> elements.  Or, if I were to use email address as the key the same physical
> entity can now change their address without me having to cascade update all
> FK instances too.  Avoiding the FK cascade when enforcing a non-ideal PK is
> a major good reason to assign a surrogate.
>
> David J.
>
>
This is exactly my point: you cannot know when a Business Rule is going to
change. Consider, for example, your Social Security number (or ID number as
we call it in South Africa). This is unique, right?. Tomorrow, however,
data of people from multiple countries gets added to your DB, and BAM! that
ID number is suddenly no longer unique. Business Rules can and do change,
and we do not know what may change in the future. Hence, it is safest to
have the surrogate in place from the start, and avoid the potential
migraine later on.

Disclaimer: this is just my opinion based on my experience (and the pain I
had to go through when Business Rules changed). I have not done any
research or conducted any studies on this.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Thu, Mar 1, 2018 at 8:52 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted <doll...@gmail.com>
> wrote:
>
>> Could you perhaps elaborate on how a surrogate key allows one to insert
>> garbage into the table? I'm afraid I don't quite get what you're saying.
>>
>
> ​A bit contrived but it makes the point:​
>
> *Company:*
> C1 (id c1)
> C2 (id c2)
>
> *Department:*
> C1-D1 (id d1)
> C1-D2 (id d2)
> C2-D1 (id d3)
> C2-D2 (id d4)
>
> *Employee:*
> C1-E1 (id e1)
> C1-E2 (id e2)
> C2-E1 (id e3)
> C2-E2 (id e4)
>
> *​Employee-Department​:*
> e1-d1
> e2-d2
> e3-d2
> e4-d4
>
> The pair e3-d2 is invalid because e3 belongs to company c2 while d2
> belongs to company c1 - but we've hidden the knowledge ​of c# behind the
> surrogate key and now we can insert garbage into employee-department.
>
> David J.
>
>
This seems like hierarchical data, where employee's parent should be
department, and department's parent is company. So it wouldn't be possible
to "insert garbage" since Company is not stored in the Employee table, only
a reference to Department (and Company determined via Department). Isn't
that how normal hierarchical data works?

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <ron.l.john...@cox.net> wrote:

> On 03/01/2018 11:47 AM, Daevor The Devoted wrote:
>
>
> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar...@aol.com>
> wrote:
>
>>
>> >Adding a surrogate key to such a table just adds overhead, although that
>> could be useful
>> >in case specific rows need updating or deleting without also modifying
>> the other rows with
>> >that same data - normally, only insertions and selections happen on such
>> tables though,
>> >and updates or deletes are absolutely forbidden - corrections happen by
>> inserting rows with
>> >an opposite transaction.
>>
>> I routinely add surrogate keys like serial col to a table already having
>> a nice candidate keys
>> to make it easy to join tables.  SQL starts looking ungainly when you
>> have a 3 col primary
>> key and need to join it with child tables.
>>
>>
> I was always of the opinion that a mandatory surrogate key (as you
> describe) is good practice.
> Sure there may be a unique key according to business logic (which may be
> consist of those "ungainly" multiple columns), but guess what, business
> logic changes, and then you're screwed!
>
>
> And so you drop the existing index and build a new one.  I've done it
> before, and I'll do it again.
>
> So using a primary key whose sole purpose is to be a primary key makes
> perfect sense to me.
>
>
> I can't stand synthetic keys.  By their very nature, they're so
> purposelessly arbitrary, and allow you to insert garbage into the table.
>
> --
> Angular momentum makes the world go 'round.
>

Could you perhaps elaborate on how a surrogate key allows one to insert
garbage into the table? I'm afraid I don't quite get what you're saying.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar  wrote:

>
> >Adding a surrogate key to such a table just adds overhead, although that
> could be useful
> >in case specific rows need updating or deleting without also modifying
> the other rows with
> >that same data - normally, only insertions and selections happen on such
> tables though,
> >and updates or deletes are absolutely forbidden - corrections happen by
> inserting rows with
> >an opposite transaction.
>
> I routinely add surrogate keys like serial col to a table already having a
> nice candidate keys
> to make it easy to join tables.  SQL starts looking ungainly when you have
> a 3 col primary
> key and need to join it with child tables.
>
>
I was always of the opinion that a mandatory surrogate key (as you
describe) is good practice.
Sure there may be a unique key according to business logic (which may be
consist of those "ungainly" multiple columns), but guess what, business
logic changes, and then you're screwed! So using a primary key whose sole
purpose is to be a primary key makes perfect sense to me.

Re: Array of foreign key

[Daevor The Devoted]

On 23 Dec 2017 9:54 pm, "Thomas Poty" <thomas.p...@gmail.com> wrote:

Good evening all,
I have just see all the messages. Thanks for that.

First, Peter Holzer has exactly understooden my need.
I am à bit disappointed this feature is not implemented. It would be great.

Then, I know enum is probably not the best choice but it is historic  in
our database. Like I said previously,  We are working on the first of two
steps migration and we will probably not use them after de second phase of
migration. You also have to know make this changes needs a lot of tests to
be sure the results will be correct and expected.! For our company it is
not so easy...

After that, we are running on pgsql 9.5 and centos 7.x.

Finally,  keep in mind we are here to help each others.

Thanks for your help

Thomas


Le 23 déc. 2017 20:25, "Peter J. Holzer" <hjp-pg...@hjp.at> a écrit :

On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pg...@hjp.at>
wrote:
> >...Is there a way to
> >enforce foreign key constraints on the members of an array?
> >At insert time you can check with a trigger of course, and maybe there
> >is a way to do it in a check constraint
>
> I don't think you understand how Foreign Key constraints work in
PostgreSQL.
> PostgreSQL will prevent any insert where the value of a column is not
within
> the FK table.
> So you DO NOT need a check constraint or trigger.
>
> What I do not understand is your reference to a FK "array".

If you do not understand something, please ask. Don't claim that other
people "don't understand how X works" just because you don't know what
they are talking about. Also, please pay a bit of attention who you
are replying to. I am not the OP. I just understand what he wants (or at
least I think I do).

> So for the sake of CLARIDICATION, would you please
> 1, State the version of PostgreSQL
> 2. State the O/S

Why should I? You didn't state the OS and PostgreSQL version you use
either. And I don't think you should, as it is irrelevant for the
discussion.

> 3. Provide an example of an FK "array" that you are concerned with.

I think the OP wants something like this:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null,
array_of_features int[]
references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

If you want foreign keys, you have to use an intersection table:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null
);

create table product_features (
product references products(id),
feature references features(id)
);

But that has a relatively high overhead both in storage and for queries.

I can understand why the OP wants that. I could have used something like
this in the past, too.

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/>



This is an interesting feature you are talking about. However, I'm not sure
I properly understand the use-case(s). Could you perhaps please give some
examples to help me understand? (I don't just mean some SQL code, I mean an
explanation of the business use-cases served by the code).

Kind regards,
Daevor, The Devoted

Re: SV: Refreshing materialized views

[Daevor The Devoted]

On 28 Nov 2017 5:18 pm, "Tom Lane" <t...@sss.pgh.pa.us> wrote:

Henrik Uggla <henrik.ug...@kristianstad.se> writes:
> The underlying tables are foreign tables. The user has been mapped to a
foreign user with select permission. I have no problem selecting from the
foreign tables or the materialized views.

[ shrug... ] WFM; if I can select from the foreign table then I can make
a materialized view that selects from it, and that refreshes without
complaint.  Admittedly, getting things set up to select from the foreign
table is trickier than it sounds: your local user needs SELECT on the
foreign table plus a mapping to some remote userid, and *on the remote
server* that remote userid needs SELECT on whatever the foreign table
is referencing.  I'm guessing you messed up one of these components.

regards, tom lane

Hendrik, perhaps an easy way to check out Tom's suggestion is to create a
very simple materialized view that selects just from one of the foreign
tables, then attempt the REFRESH. If that works, then keep adding more
tables from your original materialized view until you have found the
problem.
Basically, reduce the problem to the simplest case, and if that works, then
keep adding to it until you hit the problem. You may still not know why the
problem is happening, but you'll at least know where to focus any further
investigation.

Kind regards,
Daevor, The Devoted