Re: PostgreSQL processes use large amount of private memory on Windows

[Øystein Kolsrud]

 On Thu, Sep 17, 2020 at 7:27 PM Peter J. Holzer  wrote:

> What I would do:
>
> * Set log_statement to all (warning: that can be a lot of log messages.
>   It can also be a privacy/security hazard, depending on who has access
>   to the server and how sensitive queries are).
> * Frequently (at least once per minute) record the size of all postgres
>   processes. Send an alert if one of them is "too large".
>
> This should give you a good idea what the processes were doing at the
> time they allocated that memory, so that you can reproduce the problem.
>

In fact I already did track the memory behavior over time and recorded
memory utilization every 5 minutes. The memory consumption exhibits a
surprisingly linear growth of about 400 MB per hour. And since I can't see
any sharp points where the memory jumps I can't really identify any single
query culprit that is causing this. It seems more like there are a large
number of small queries that are causing the memory to stack up over time.

And again, the real mystery here is that we don't see this behavior in most
deployments of the product which all utilize the database in the same way.
This leads me to think that there is some configuration on the server side
that is somehow affecting memory allocation. It's almost as if the shared
memory allocation between processes is not working and that all processes
are holding a copy of the shared memory instead of actually sharing.

-- 
Best regards, Øystein Kolsrud

On Thu, Sep 17, 2020 at 7:27 PM Peter J. Holzer  wrote:

> On 2020-09-17 11:19:07 +0200, Øystein Kolsrud wrote:
> > I have a product that uses PostgreSQL (v9.6) as database on Windows, and
> we
> > often observe that the "shared working set" memory reported for the
> individual
> > connection processes is quite high (around 200MB), but the "private
> working
> > set" is relatively low (around 5 MB). I assume this is due to the
> processes
> > utilizing the shared buffers of Postgres, and in these cases everything
> works
> > fine as the total memory consumption on the system is quite low. But for
> some
> > deployments we have observed that the "private working set" is around
> 200 MB as
> > well, and in these cases the server quickly runs out of memory when the
> number
> > of connections rises. I have never been able to reproduce this behavior
> myself.
> [...]
> > All settings typically referred to (like "work_mem" and "temp_buffers")
> are at
> > default settings, and I have a very hard time seeing how those settings
> could
> > add up to memory in the magnitude of 200MB.
>
> The work_mem setting is per operation (e.g. sort, hash join, etc.), so a
> complex query may use a multiple of it. However, the default is just 4 MB,
> so a query would have to be very complex to use 50 times as much. Also,
> that memory is freed after the query (however "freed" does not
> necessarily mean "returned to the OS", it can just mean "marked as
> availiable for reuse" - that depends on the C library and the OS).
>
> What I would do:
>
> * Set log_statement to all (warning: that can be a lot of log messages.
>   It can also be a privacy/security hazard, depending on who has access
>   to the server and how sensitive queries are).
> * Frequently (at least once per minute) record the size of all postgres
>   processes. Send an alert if one of them is "too large".
>
> This should give you a good idea what the processes were doing at the
> time they allocated that memory, so that you can reproduce the problem.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>


-- 
Mvh Øystein Kolsrud

Re: multiple tables got corrupted

[Magnus Hagander]

That depends on what the problem is and how they fix it. Most likely yes --
especially since if you haven't enabled data checksums you won't *know* if
things are OK or not. So I'd definitely recommend it even if things *look*
OK.

//Magnus


On Wed, Sep 16, 2020 at 5:06 AM Vasu Madhineni 
wrote:

> I could see block read I/O errors in /var/log/syslog. if those error fixed
> by OS team, will it require recovery.
>
> Also can i use LIMIT and OFFSET to locate corrupted rows?
>
> Thanks in advance
>
> Regards,
> Vasu Madhineni
>
> On Wed, Sep 16, 2020, 01:58 Magnus Hagander  wrote:
>
>> Try reading them "row by row" until it breaks. That is, SELECT * FROM ...
>> LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search starting
>> at what seems like a reasonable place looking at the size of the table vs
>> the first failed block to make it faster, but the principle is the same.
>> Once it fails, you've found a corrupt block...
>>
>> //Magnus
>>
>>
>> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni 
>> wrote:
>>
>>> Is it possible to identify which rows are corrupted in particular tables.
>>>
>>> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander 
>>> wrote:
>>>


 On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni 
 wrote:

> Hi All,
>
> In one of my postgres databases multiple tables got corrupted and
> followed the below steps but still the same error.
>
> 1.SET zero_damaged_pages = on
> 2. VACUUM ANALYZE, VACUUM FULL
> but still same error.
>


 That is a very destructive first attempt. I hope you took a full
 disk-level backup of the database before you did that, as it can ruin your
 chances for forensics and data recovery for other issues.


 moh_fa=# VACUUM FULL;
> ERROR:  could not read block 9350 in file "base/1156523/1270812":
> Input/output error
>
> Tried to take backup of tables with pg_dump but same error. files
> exist physically in base location.
>
> How to proceed on this, no backup to restore.
>
>
 This is clearly some sort of disk error, and with no backups to restore
 you will definitely be losing data.

 I'd start by figuring out which tables have no corruption and do work,
 and back those up (with pg_dump for example) as soon as possible to a
 different machine -- since it's not exactly unlikely that further disk
 errors will appear.

 Once you've done that, identify the tables, and then try to do partial
 recovery. For example, if you look at the file 1270812, how big it is?
 PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
 file. If this is at the very end of the file, you can for example try to
 get the data out until that point with LIMIT. If it's in the middle of the
 file, it gets more ticky, but similar approaches can be done.

 Also, unless you are running with data checksums enabled, I wouldn't
 fully trust the data in the tables that you *can* read either. Since you
 clearly have disk issues, they may have caused corruption elsewhere as
 well, so whatever verification you can do against other tables, you should
 do as well.


 You'll of course also want to check any kernel logs or storage system
 logs to see if they can give you a hint as to what happened, but they are
 unlikely to actually give you something that will help you fix the problem.

Re: multiple tables got corrupted

[Vasu Madhineni]

Hi Magnus,

Thanks for your update.
To identify the number of tables corrupted in the database if I run below
command, Will any impact on other tables in the production environment.

"pg_dump -f /dev/null database"

Thanks in advance.

Regards,
Vasu Madhineni

On Fri, Sep 18, 2020 at 3:42 PM Magnus Hagander  wrote:

> That depends on what the problem is and how they fix it. Most likely yes
> -- especially since if you haven't enabled data checksums you won't *know*
> if things are OK or not. So I'd definitely recommend it even if things
> *look* OK.
>
> //Magnus
>
>
> On Wed, Sep 16, 2020 at 5:06 AM Vasu Madhineni 
> wrote:
>
>> I could see block read I/O errors in /var/log/syslog. if those error
>> fixed by OS team, will it require recovery.
>>
>> Also can i use LIMIT and OFFSET to locate corrupted rows?
>>
>> Thanks in advance
>>
>> Regards,
>> Vasu Madhineni
>>
>> On Wed, Sep 16, 2020, 01:58 Magnus Hagander  wrote:
>>
>>> Try reading them "row by row" until it breaks. That is, SELECT * FROM
>>> ... LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search
>>> starting at what seems like a reasonable place looking at the size of the
>>> table vs the first failed block to make it faster, but the principle is the
>>> same. Once it fails, you've found a corrupt block...
>>>
>>> //Magnus
>>>
>>>
>>> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni 
>>> wrote:
>>>
 Is it possible to identify which rows are corrupted in particular
 tables.

 On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander 
 wrote:

>
>
> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni <
> vasumdba1...@gmail.com> wrote:
>
>> Hi All,
>>
>> In one of my postgres databases multiple tables got corrupted and
>> followed the below steps but still the same error.
>>
>> 1.SET zero_damaged_pages = on
>> 2. VACUUM ANALYZE, VACUUM FULL
>> but still same error.
>>
>
>
> That is a very destructive first attempt. I hope you took a full
> disk-level backup of the database before you did that, as it can ruin your
> chances for forensics and data recovery for other issues.
>
>
> moh_fa=# VACUUM FULL;
>> ERROR:  could not read block 9350 in file "base/1156523/1270812":
>> Input/output error
>>
>> Tried to take backup of tables with pg_dump but same error. files
>> exist physically in base location.
>>
>> How to proceed on this, no backup to restore.
>>
>>
> This is clearly some sort of disk error, and with no backups to
> restore you will definitely be losing data.
>
> I'd start by figuring out which tables have no corruption and do work,
> and back those up (with pg_dump for example) as soon as possible to a
> different machine -- since it's not exactly unlikely that further disk
> errors will appear.
>
> Once you've done that, identify the tables, and then try to do partial
> recovery. For example, if you look at the file 1270812, how big it is?
> PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
> file. If this is at the very end of the file, you can for example try to
> get the data out until that point with LIMIT. If it's in the middle of the
> file, it gets more ticky, but similar approaches can be done.
>
> Also, unless you are running with data checksums enabled, I wouldn't
> fully trust the data in the tables that you *can* read either. Since you
> clearly have disk issues, they may have caused corruption elsewhere as
> well, so whatever verification you can do against other tables, you should
> do as well.
>
>
> You'll of course also want to check any kernel logs or storage system
> logs to see if they can give you a hint as to what happened, but they are
> unlikely to actually give you something that will help you fix the 
> problem.
>
>

Re: multiple tables got corrupted

[Jerry Sievers]

Vasu Madhineni  writes:

> Hi Magnus,
>
> Thanks for your update.
> To identify the number of tables corrupted in the database if I run
> below command, Will any impact on other tables in the production
> environment. 
>
> "pg_dump -f /dev/null database"

Consider using pg_dump or any other means to dump *each* table
individually.

pg_dump is going to abort on the first case of corruption in any table
that results in a read error on full scan, thus in a scenario where
multiple corrupt tables is likely, you're not going to get too far
w/monolithic approach.



>
> Thanks in advance.
>
> Regards,
> Vasu Madhineni
>
> On Fri, Sep 18, 2020 at 3:42 PM Magnus Hagander 
> wrote:
>
> That depends on what the problem is and how they fix it. Most
> likely yes -- especially since if you haven't enabled data
> checksums you won't *know* if things are OK or not. So I'd
> definitely recommend it even if things *look* OK.
>
> //Magnus
>
>
> On Wed, Sep 16, 2020 at 5:06 AM Vasu Madhineni <
> vasumdba1...@gmail.com> wrote:
>
> I could see block read I/O errors in /var/log/syslog. if
> those error fixed by OS team, will it require recovery.
>
> Also can i use LIMIT and OFFSET to locate corrupted rows?
>
> Thanks in advance
>
> Regards,
> Vasu Madhineni
>
> On Wed, Sep 16, 2020, 01:58 Magnus Hagander <
> mag...@hagander.net> wrote:
>
> Try reading them "row by row" until it breaks. That is,
> SELECT * FROM ... LIMIT 1, then LIMIT 2 etc. For more
> efficiency use a binary search starting at what seems
> like a reasonable place looking at the size of the table
> vs the first failed block to make it faster, but the
> principle is the same. Once it fails, you've found a
> corrupt block...
>
> //Magnus
>
>
> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni <
> vasumdba1...@gmail.com> wrote:
>
> Is it possible to identify which rows are corrupted
> in particular tables.
>
> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander <
> mag...@hagander.net> wrote:
>
>
>
> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni <
> vasumdba1...@gmail.com> wrote:
>
> Hi All,
>
> In one of my postgres databases multiple
> tables got corrupted and followed the below
> steps but still the same error.
>
> 1.SET zero_damaged_pages = on
> 2. VACUUM ANALYZE, VACUUM FULL
> but still same error. 
>
>
>
> That is a very destructive first attempt. I hope
> you took a full disk-level backup of the database
> before you did that, as it can ruin your chances
> for forensics and data recovery for other issues.
>
>
>
> moh_fa=# VACUUM FULL;
> ERROR:  could not read block 9350 in file
> "base/1156523/1270812": Input/output error
>
> Tried to take backup of tables with pg_dump
> but same error. files exist physically in
> base location.
>
> How to proceed on this, no backup to restore.
>
>
>
> This is clearly some sort of disk error, and with
> no backups to restore you will definitely be
> losing data.
>
> I'd start by figuring out which tables have no
> corruption and do work, and back those up (with
> pg_dump for example) as soon as possible to a
> different machine -- since it's not exactly
> unlikely that further disk errors will appear.
>
> Once you've done that, identify the tables, and
> then try to do partial recovery. For example, if
> you look at the file 1270812, how big it is?
> PostgreSQL is failing to read block 9350 which
> is 76595200 bytes into the file. If this is at
> the very end of the file, you can for example try
> to get the dat

How to write such a query

[Igor Korot]

Hi,
Consider following

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

How do I write a WHERE clause in the

[code]
UPDATE X.field1 SET X.field1 = '' WHERE
[/code]

Thank you.

Re: How to write such a query

[Jonathan Strong]

Are you looking to arbitrarily update the field in the fifth row, or can
the row that needs to be updated be isolated by some add'l attribute?
What's the use case?

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 1:27 PM Igor Korot  wrote:

> Hi,
> Consider following
>
> [code]
> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> [/code]
>
> Assuming that the SELECT return 10 rows, I want to update X.field1
> in row 5.
>
> How do I write a WHERE clause in the
>
> [code]
> UPDATE X.field1 SET X.field1 = '' WHERE
> [/code]
>
> Thank you.
>
>
>

Re: How to write such a query

[Paul Förster]

Hi Igor,

> On 18. Sep, 2020, at 19:29, Igor Korot  wrote:
> 
> Hi,
> Consider following
> 
> [code]
> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> [/code]
> 
> Assuming that the SELECT return 10 rows, I want to update X.field1
> in row 5.
> 
> How do I write a WHERE clause in the
> 
> [code]
> UPDATE X.field1 SET X.field1 = '' WHERE
> [/code]
> 
> Thank you.

update x set field1='' where id=5;

Cheers,
Paul

Re: How to write such a query

[Igor Korot]

Hi, Paul

On Fri, Sep 18, 2020 at 12:34 PM Paul Förster  wrote:
>
> Hi Igor,
>
> > On 18. Sep, 2020, at 19:29, Igor Korot  wrote:
> >
> > Hi,
> > Consider following
> >
> > [code]
> > CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> > CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
> > SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> > [/code]
> >
> > Assuming that the SELECT return 10 rows, I want to update X.field1
> > in row 5.
> >
> > How do I write a WHERE clause in the
> >
> > [code]
> > UPDATE X.field1 SET X.field1 = '' WHERE
> > [/code]
> >
> > Thank you.
>
> update x set field1='' where id=5;

How do you know that the row #5 will have an X.id field 5?

Thank you.

>
> Cheers,
> Paul

Re: How to write such a query

[Igor Korot]

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong 
wrote:

> Are you looking to arbitrarily update the field in the fifth row, or can
> the row that needs to be updated be isolated by some add'l attribute?
> What's the use case?
>

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

Thank you.


> - Jon
>
> 
> 
>
> *Jonathan Strong*
>
> CIO / CTO / Consultant
>
> *P:* 609-532-1715 *E:* jonathanrstr...@gmail.com
>
> *Quora Top Writer *
>
>
> On Fri, Sep 18, 2020 at 1:27 PM Igor Korot  wrote:
>
>> Hi,
>> Consider following
>>
>> [code]
>> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
>> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
>> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
>> [/code]
>>
>> Assuming that the SELECT return 10 rows, I want to update X.field1
>> in row 5.
>>
>> How do I write a WHERE clause in the
>>
>> [code]
>> UPDATE X.field1 SET X.field1 = '' WHERE
>> [/code]
>>
>> Thank you.
>>
>>
>>

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong 
mailto:jonathanrstr...@gmail.com>> wrote:


Are you looking to arbitrarily update the field in the fifth row, or
can the row that needs to be updated be isolated by some add'l
attribute? What's the use case?


What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a 
form
with the arbitrary query, then you can go to any record in that form and 
update any field.


Is it even possible from the "pure SQL" POV? Or Access is doing some 
VBA/DB/4GL magic?




When you are updating a record in a form the framework(Access in your 
case) is using some identifier from that record to UPDATE that 
particular record in the database. From when I used Access, I seem to 
remember it would not give you INSERT/UPDATE capability on a form unless 
you had specified some unique key for the records. So you need to find 
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.



Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Jonathan Strong]

@Adrian -

Using a unique key value or otherwise isolating a specific record via
selection against values in its attributes is certainly preferable to
choosing a row to update via its position in a result set, unless the use
case actually makes use of that position info as a meaningful descriptor of
the data in some fashion.

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 1:58 PM Adrian Klaver 
wrote:

> On 9/18/20 10:46 AM, Igor Korot wrote:
> > Hi, Johnathan,
> >
> > On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> > mailto:jonathanrstr...@gmail.com>> wrote:
> >
> > Are you looking to arbitrarily update the field in the fifth row, or
> > can the row that needs to be updated be isolated by some add'l
> > attribute? What's the use case?
> >
> >
> > What do you mean?
> > I don't have any other attributes.
> >
> > I want to understand how to emulate MS Access behavior, where you have a
> > form
> > with the arbitrary query, then you can go to any record in that form and
> > update any field.
> >
> > Is it even possible from the "pure SQL" POV? Or Access is doing some
> > VBA/DB/4GL magic?
> >
>
> When you are updating a record in a form the framework(Access in your
> case) is using some identifier from that record to UPDATE that
> particular record in the database. From when I used Access, I seem to
> remember it would not give you INSERT/UPDATE capability on a form unless
> you had specified some unique key for the records. So you need to find
> what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.
>
> > Thank you.
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Thomas Kellerer]

Igor Korot schrieb am 18.09.2020 um 19:29:

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.


There is no such thing as "row 5" in a relational database.

Rows in a table have no inherent sort order. The only way you can identify
a row, is by the value of its primary (or unique) key. Not by "position".

The only way you can identify "row 5" is, if you use an ORDER BY to
define a sort order on the result - but that position is only valid
for that _result_, it has no meaning for the actual table data.

Which brings us back to the fact, that the only way to (uniquely) identify
a row in a table is to specify its primary key value in the WHERE clause

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 11:13 AM, Jonathan Strong wrote:

@Adrian -

Using a unique key value or otherwise isolating a specific record via 
selection against values in its attributes is certainly preferable to 
choosing a row to update via its position in a result set, unless the 
use case actually makes use of that position info as a meaningful 
descriptor of the data in some fashion.



The bigger issue is deciding what attribute of the selected row is be 
used to do the UPDATE. Unless it is the PRIMARY KEY(or other UNIQUE 
key(s)) then you very likely are going to UPDATE more then you bargained 
for.




- Jon

 



*Jonathan Strong*




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Jonathan Strong]

Yes...absolutely. Short of using ORDER BY, the order of a multi-row result
set can be arbitrary, with "row position" having no significant meaning.

This gets back to understanding set theory, the relational model, the
various types of keys (primary, candidate, foreign, etc.). Truly crucial to
understand the model in order to write correctly functioning and reliable
code.

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer  wrote:

> Igor Korot schrieb am 18.09.2020 um 19:29:
> > [code]
> > CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> > CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10,
> 2));
> > SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> > [/code]
> >
> > Assuming that the SELECT return 10 rows, I want to update X.field1
> > in row 5.
>
> There is no such thing as "row 5" in a relational database.
>
> Rows in a table have no inherent sort order. The only way you can identify
> a row, is by the value of its primary (or unique) key. Not by "position".
>
> The only way you can identify "row 5" is, if you use an ORDER BY to
> define a sort order on the result - but that position is only valid
> for that _result_, it has no meaning for the actual table data.
>
> Which brings us back to the fact, that the only way to (uniquely) identify
> a row in a table is to specify its primary key value in the WHERE clause
>
>
>
>
>
>

Re: How to write such a query

[Jonathan Strong]

Yes - 100%

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 2:22 PM Adrian Klaver 
wrote:

> On 9/18/20 11:13 AM, Jonathan Strong wrote:
> > @Adrian -
> >
> > Using a unique key value or otherwise isolating a specific record via
> > selection against values in its attributes is certainly preferable to
> > choosing a row to update via its position in a result set, unless the
> > use case actually makes use of that position info as a meaningful
> > descriptor of the data in some fashion.
>
>
> The bigger issue is deciding what attribute of the selected row is be
> used to do the UPDATE. Unless it is the PRIMARY KEY(or other UNIQUE
> key(s)) then you very likely are going to UPDATE more then you bargained
> for.
>
> >
> > - Jon
> >
> >  <
> https://www.jonathanrstrong.com>
> >
> >
> >
> > *Jonathan Strong*
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Igor Korot]

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:
>
> On 9/18/20 10:46 AM, Igor Korot wrote:
> > Hi, Johnathan,
> >
> > On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> > mailto:jonathanrstr...@gmail.com>> wrote:
> >
> > Are you looking to arbitrarily update the field in the fifth row, or
> > can the row that needs to be updated be isolated by some add'l
> > attribute? What's the use case?
> >
> >
> > What do you mean?
> > I don't have any other attributes.
> >
> > I want to understand how to emulate MS Access behavior, where you have a
> > form
> > with the arbitrary query, then you can go to any record in that form and
> > update any field.
> >
> > Is it even possible from the "pure SQL" POV? Or Access is doing some
> > VBA/DB/4GL magic?
> >
>
> When you are updating a record in a form the framework(Access in your
> case) is using some identifier from that record to UPDATE that
> particular record in the database. From when I used Access, I seem to
> remember it would not give you INSERT/UPDATE capability on a form unless
> you had specified some unique key for the records. So you need to find
> what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

Thank you.

>
> > Thank you.
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 11:49 AM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:


On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
mailto:jonathanrstr...@gmail.com>> wrote:

 Are you looking to arbitrarily update the field in the fifth row, or
 can the row that needs to be updated be isolated by some add'l
 attribute? What's the use case?


What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?



When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.


But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?


You defined them:

CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));

How you fetch that value is going to depend on where the record set is 
being presented and how the record to be UPDATEd is selected? If you are 
using some sort of framework/form to display the records it will have 
some mechanism to gather the information(data) on select and then you 
can use the PRIMARY KEY value from that data to do the UPDATE. If you 
want a more precise answer then you will need to provide a complete 
example of what you are doing.




Thank you.




Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Jonathan Strong]

A pretty good read / intro to the concept of keys in the relational model:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/primary-key-primer-for-sql-server/

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 3:08 PM Adrian Klaver 
wrote:

> On 9/18/20 11:49 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
> >  wrote:
> >>
> >> On 9/18/20 10:46 AM, Igor Korot wrote:
> >>> Hi, Johnathan,
> >>>
> >>> On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> >>> mailto:jonathanrstr...@gmail.com>> wrote:
> >>>
> >>>  Are you looking to arbitrarily update the field in the fifth row,
> or
> >>>  can the row that needs to be updated be isolated by some add'l
> >>>  attribute? What's the use case?
> >>>
> >>>
> >>> What do you mean?
> >>> I don't have any other attributes.
> >>>
> >>> I want to understand how to emulate MS Access behavior, where you have
> a
> >>> form
> >>> with the arbitrary query, then you can go to any record in that form
> and
> >>> update any field.
> >>>
> >>> Is it even possible from the "pure SQL" POV? Or Access is doing some
> >>> VBA/DB/4GL magic?
> >>>
> >>
> >> When you are updating a record in a form the framework(Access in your
> >> case) is using some identifier from that record to UPDATE that
> >> particular record in the database. From when I used Access, I seem to
> >> remember it would not give you INSERT/UPDATE capability on a form unless
> >> you had specified some unique key for the records. So you need to find
> >> what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.
> >
> > But now the question becomes
> >
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
>
> You defined them:
>
> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
>
> How you fetch that value is going to depend on where the record set is
> being presented and how the record to be UPDATEd is selected? If you are
> using some sort of framework/form to display the records it will have
> some mechanism to gather the information(data) on select and then you
> can use the PRIMARY KEY value from that data to do the UPDATE. If you
> want a more precise answer then you will need to provide a complete
> example of what you are doing.
>
> >
> > Thank you.
> >
> >>
> >>> Thank you.
> >>>
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Ron]

On 9/18/20 1:49 PM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
mailto:jonathanrstr...@gmail.com>> wrote:

 Are you looking to arbitrarily update the field in the fifth row, or
 can the row that needs to be updated be isolated by some add'l
 attribute? What's the use case?


What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?


When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?


You're missing the point: as mentioned before, there is no "row 5". To 
update the 5th record that you've fetched, you increment a counter each time 
you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg' 
WHERE id = ;


--
Angular momentum makes the world go 'round.

Re: How to write such a query

[Ken Tanzer]

>
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
>
> You're missing the point: as mentioned before, there is no "row 5". To
> update the 5th record that you've fetched, you increment a counter each
> time
> you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg'
> WHERE id = ;
>
>
It seems worth mentioning for benefit of the OPs question that there _is_ a
way to get a row number within a result set.  Understanding and making good
use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE X.id =
Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it might
or might not have any meaning.  But if you queried with a primary key and a
row number, you could then tie the two together and make an update based on
that.

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[Rob Sargent]

> On Sep 18, 2020, at 1:45 PM, Ken Tanzer  wrote:
> 
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
> 
> You're missing the point: as mentioned before, there is no "row 5". To 
> update the 5th record that you've fetched, you increment a counter each time 
> you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg' 
> WHERE id = ;
> 
> 
> It seems worth mentioning for benefit of the OPs question that there _is_ a 
> way to get a row number within a result set.  Understanding and making good 
> use of that is an additional matter.
> 
> SELECT X.field1, Y.field2,row_number() OVER ()  from X, Y WHERE X.id = Y.id 
> -- ORDER BY ?
> 
> That row number is going to depend on the order of the query, so it might or 
> might not have any meaning.  But if you queried with a primary key and a row 
> number, you could then tie the two together and make an update based on that.

If “row 5” as seen by the OP has no distinguishing characteristic directing OP 
to edit that tuple then he’s in a world of hurt, well beyond the reach of 
anyone here.

Re: How to write such a query

[Igor Korot]

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer  wrote:

> > How to find what the primary key (or UNIQUE identifier) value is
>> > for row 5 in the recordset?
>>
>> You're missing the point: as mentioned before, there is no "row 5". To
>> update the 5th record that you've fetched, you increment a counter each
>> time
>> you fetch a row, and when you read #5, do an UPDATE X SET field1 =
>> 'blarg'
>> WHERE id = ;
>>
>>
> It seems worth mentioning for benefit of the OPs question that there _is_
> a way to get a row number within a result set.  Understanding and making
> good use of that is an additional matter.
>
> SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE X.id =
> Y.id -- ORDER BY ?
>
> That row number is going to depend on the order of the query, so it might
> or might not have any meaning.  But if you queried with a primary key and a
> row number, you could then tie the two together and make an update based on
> that.
>

Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I
presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the
resulting recordset.

Access does it, PowerBuilder does it.

I just want to understand how.

Thank you.


> Cheers,
> Ken
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: How to write such a query

[Thomas Kellerer]

Igor Korot schrieb am 18.09.2020 um 22:18:

Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the 
resulting recordset.

Access does it, PowerBuilder does it.


I assume that they query the database to find the primary key for the table in 
question.
Once the primary key columns are known (and part of the result), they generate 
the appropriate
UPDATE statement with a WHERE clause based on the the values in the result to 
update
the row that was changed.

Thomas

Re: How to write such a query

[Ron]

On 9/18/20 3:18 PM, Igor Korot wrote:

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer > wrote:


> How to find what the primary key (or UNIQUE identifier) value is
> for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row
5". To
update the 5th record that you've fetched, you increment a counter
each time
you fetch a row, and when you read #5, do an UPDATE X SET field1 =
'blarg'
WHERE id = ;


It seems worth mentioning for benefit of the OPs question that there
_is_ a way to get a row number within a result set.  Understanding and
making good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()* from X, Y WHERE X.id
= Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it
might or might not have any meaning. But if you queried with a primary
key and a row number, you could then tie the two together and make an
update based on that.


Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the 
resulting recordset.


Access does it, PowerBuilder does it.

I just want to understand how.


They do it by hiding the details from you.

--
Angular momentum makes the world go 'round.

Re: How to write such a query

[Ken Tanzer]

On Fri, Sep 18, 2020 at 1:26 PM Ron  wrote:

> On 9/18/20 3:18 PM, Igor Korot wrote:
>
Thank you for the info.
> My problem is that I want to emulate Access behavior.
>
> As I said - Access does it without changing the query internally (I
> presume).
>
> I want to do the same with PostgreSQL.
>
> I'm just trying to understand how to make it work for any query
>
> I can have 3,4,5 tables, query them and then update the Nth record in the
> resulting recordset.
>
> Access does it, PowerBuilder does it.
>
> I just want to understand how.
>
>
> They do it by hiding the details from you.
>
>
That's true.  And Igor--people are asking you some good questions about why
and design and such that you'd probably be well-advised to think about and
respond to.

So I'm not saying you should do this, but responding to your question
specifically, and what the "details" are that Ron alludes to, one way to
get the result you're asking about is to run your query adding on row
numbers (pay attention to your ordering!), and then reference that result
set from an update to get the primary key you want.  So I didn't test it,
but something roughly like this:

WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y WHERE
X.id = Y.id ) UPDATE x SET ...  FROM tmp WHERE
 tmp.row_number=5 AND x.field1=tmp.field1;

Cheers,
Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[David G. Johnston]

On Fri, Sep 18, 2020 at 1:18 PM Igor Korot  wrote:

> As I said - Access does it without changing the query internally (I
> presume).
>
> I want to do the same with PostgreSQL.
>

I suspect they basically do the equivalent of:

UPDATE ... WHERE CURRENT OF ;

https://www.postgresql.org/docs/12/sql-update.html

David J.

Re: How to write such a query

[Igor Korot]

Ken,

On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer  wrote:

> On Fri, Sep 18, 2020 at 1:26 PM Ron  wrote:
>
>> On 9/18/20 3:18 PM, Igor Korot wrote:
>>
> Thank you for the info.
>> My problem is that I want to emulate Access behavior.
>>
>> As I said - Access does it without changing the query internally (I
>> presume).
>>
>> I want to do the same with PostgreSQL.
>>
>> I'm just trying to understand how to make it work for any query
>>
>> I can have 3,4,5 tables, query them and then update the Nth record in the
>> resulting recordset.
>>
>> Access does it, PowerBuilder does it.
>>
>> I just want to understand how.
>>
>>
>> They do it by hiding the details from you.
>>
>>
> That's true.  And Igor--people are asking you some good questions about
> why and design and such that you'd probably be well-advised to think about
> and respond to.
>
> So I'm not saying you should do this, but responding to your question
> specifically, and what the "details" are that Ron alludes to, one way to
> get the result you're asking about is to run your query adding on row
> numbers (pay attention to your ordering!), and then reference that result
> set from an update to get the primary key you want.  So I didn't test it,
> but something roughly like this:
>
> WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y
> WHERE X.id = Y.id ) UPDATE x SET ...  FROM tmp WHERE
>  tmp.row_number=5 AND x.field1=tmp.field1;
>

I didn't know that row_number() function exists and it is available across
different DBMSes.

I will test that query later.

Thank you.

Now one other little thing: could you point me to the documentation that
explains the meaning of the "window function"?



> Cheers,
> Ken
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: How to write such a query

[Ken Tanzer]

On Fri, Sep 18, 2020 at 3:09 PM Igor Korot  wrote:

>
> Now one other little thing: could you point me to the documentation that
> explains the meaning of the "window function"?
>

Can I point you to Google instead?

https://www.google.com/search?q=postgresql+window+functions

Cheers,
Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 1:18 PM, Igor Korot wrote:

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer > wrote:


 > How to find what the primary key (or UNIQUE identifier) value is
 > for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row
5". To
update the 5th record that you've fetched, you increment a
counter each time
you fetch a row, and when you read #5, do an UPDATE X SET field1
= 'blarg'
WHERE id = ;


It seems worth mentioning for benefit of the OPs question that there
_is_ a way to get a row number within a result set.  Understanding
and making good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE
X.id = Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it
might or might not have any meaning.  But if you queried with a
primary key and a row number, you could then tie the two together
and make an update based on that.


Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I 
presume).


I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in 
the resulting recordset.


You mean you are doing a join over 5 tables and then updating some 
record that is the output of the join?


If so are you updating all the values or a value or some portion of the 
values?


This is being done in a form or in the query builder?



Access does it, PowerBuilder does it.

I just want to understand how.

Thank you.


Cheers,
Ken
-- 
AGENCY Software

A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.




--
Adrian Klaver
adrian.kla...@aklaver.com