Re: Paging through table one row at a ttime
On 07/20/2022 03:09 PM, H wrote: > I am running postgres 13 under CentOS 7. I have a need to be able to page > through a table one row at a time, possibly using pspg (or other tool) > allowing me to move in either direction in the table one single row at a > time. Ideally only the columns in the selected row should be visible, even > better if there would be one column of that row per row in the terminal > window but that would probably be wishing for too much. > > Is there any configuration of pspg that would allow me to do the above? > > > I have not seen any replies to my question above - is this perhaps not possible to do?
Re: Paging through table one row at a ttime
> On Jul 22, 2022, at 6:31 AM, H wrote: > > On 07/20/2022 03:09 PM, H wrote: >> I am running postgres 13 under CentOS 7. I have a need to be able to page >> through a table one row at a time, possibly using pspg (or other tool) >> allowing me to move in either direction in the table one single row at a >> time. Ideally only the columns in the selected row should be visible, even >> better if there would be one column of that row per row in the terminal >> window but that would probably be wishing for too much. >> >> Is there any configuration of pspg that would allow me to do the above? >> >> >> > I have not seen any replies to my question above - is this perhaps not > possible to do? > > I didn’t follow the “even better”. Aside from that most of us are simple sql users and data managers - we don’t go back and forth row by row all that often. Have you found the LIMIT and OFFSET features of SELECT statement? After that your in application space and not this forum perhaps >
Re: Paging through table one row at a ttime
On Fri, Jul 22, 2022 at 07:05:31AM -0600, Rob Sargent wrote: > > > > On Jul 22, 2022, at 6:31 AM, H wrote: > > > > On 07/20/2022 03:09 PM, H wrote: > >> I am running postgres 13 under CentOS 7. I have a need to be able to page > >> through a table one row at a time, possibly using pspg (or other tool) > >> allowing me to move in either direction in the table one single row at a > >> time. Ideally only the columns in the selected row should be visible, even > >> better if there would be one column of that row per row in the terminal > >> window but that would probably be wishing for too much. > >> > >> Is there any configuration of pspg that would allow me to do the above? > >> > >> > >> > > I have not seen any replies to my question above - is this perhaps not > > possible to do? > > > > > I didn’t follow the “even better”. Aside from that most of us are simple sql > users and data managers - we don’t go back and forth row by row all that > often. Have you found the LIMIT and OFFSET features of SELECT statement? > After that your in application space and not this forum perhaps Yeah, I was confused by the question. I originally thought you were asking about pagination, and I have several blog entries about that: https://momjian.us/main/blogs/pgblog/2020.html#August_17_2020 but it then sounded like you were looking for a terminal-level API, which kind of sounded like pspg, which you are using, but something much more sophisticated. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: Paging through table one row at a ttime
On 07/22/2022 03:14 PM, Bruce Momjian wrote: > On Fri, Jul 22, 2022 at 07:05:31AM -0600, Rob Sargent wrote: >> >>> On Jul 22, 2022, at 6:31 AM, H wrote: >>> >>> On 07/20/2022 03:09 PM, H wrote: I am running postgres 13 under CentOS 7. I have a need to be able to page through a table one row at a time, possibly using pspg (or other tool) allowing me to move in either direction in the table one single row at a time. Ideally only the columns in the selected row should be visible, even better if there would be one column of that row per row in the terminal window but that would probably be wishing for too much. Is there any configuration of pspg that would allow me to do the above? >>> I have not seen any replies to my question above - is this perhaps not >>> possible to do? >>> >>> >> I didn’t follow the “even better”. Aside from that most of us are simple sql >> users and data managers - we don’t go back and forth row by row all that >> often. Have you found the LIMIT and OFFSET features of SELECT statement? >> After that your in application space and not this forum perhaps > Yeah, I was confused by the question. I originally thought you were > asking about pagination, and I have several blog entries about that: > > https://momjian.us/main/blogs/pgblog/2020.html#August_17_2020 > > but it then sounded like you were looking for a terminal-level API, > which kind of sounded like pspg, which you are using, but something much > more sophisticated. > Let me flesh out my need a little bit more. I have a need for a quick-and-dirty solution to paging through a postgresql table. pspg almost fits that need but could be even better. Using pspg I can either: (1) view one table row in each pspg terminal window row using the pspg flag to shorten columns, or (2) list as much of the table row in the pspg terminal window as fits. Depending on the size of the row, this could mean less than one table row or more than one table row. To make it easier to view individual table rows in eg. pspg I would like to view exactly one table row, not more, and ideally each column of that table row would use individual pspg terminal rows. I hope that makes it clearer. Hoping this might be possible, either using pspg or other similar software. I am not writing any application code.
Re: Paging through table one row at a ttime
On Fri, Jul 22, 2022 at 04:33:14PM +0200, H wrote: > Let me flesh out my need a little bit more. I have a need for a > quick-and-dirty solution to paging through a postgresql table. pspg > almost fits that need but could be even better. Using pspg I can > either: (1) view one table row in each pspg terminal window row using > the pspg flag to shorten columns, or (2) list as much of the table row > in the pspg terminal window as fits. Depending on the size of the row, > this could mean less than one table row or more than one table row. > > To make it easier to view individual table rows in eg. pspg I would > like to view exactly one table row, not more, and ideally each column > of that table row would use individual pspg terminal rows. > > I hope that makes it clearer. Hoping this might be possible, either > using pspg or other similar software. I am not writing any application > code. I would ask the pspg author on something that specific. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
How does postgres sort large strings?
Hi!
I thought that the sorting values are stored entirely in work_mem, but in fact
it works somehow differently.
Can anyone suggest how this works?
For example, I created this 512MB incompressible file and test table:
tr -dcs '[:print:]' '[:print:]' < /dev/urandom | tr -d '"'\' | dd bs=1K
count=512K of=asciidump
create unlogged table t1 (v text);
insert into t1 select pg_read_file('asciidump') from generate_series(1, 10);
select pg_column_size(v), octet_length(v) from t1 limit 1;
pg_column_size | octet_length
+--
536870912 |536870912
set work_mem to '64MB';
Now I think that 64MB is not enough to sort such large values and postgres will
use temp files,
but in fact it does not.
select temp_files, temp_bytes from pg_stat_database where datname =
current_catalog;
temp_files | temp_bytes
+
0 | 0
explain (analyze,verbose,buffers) select v from t1 order by v;
QUERY PLAN
---
Sort (cost=94.38..97.78 rows=1360 width=32) (actual time=6433.138..6433.140
rows=10 loops=1)
Output: v
Sort Key: t1.v
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=543881 read=679794 written=118012
-> Seq Scan on public.t1 (cost=0.00..23.60 rows=1360 width=32) (actual
time=0.007..0.009 rows=10 loops=1)
Output: v
Buffers: shared hit=1
Planning Time: 0.035 ms
Execution Time: 6433.155 ms
> Sort Method: quicksort Memory: 25kB
select temp_files, temp_bytes from pg_stat_database where datname =
current_catalog;
temp_files | temp_bytes
+
0 | 0
WOW! How does it work?! :-)
--
Sergey Burladyan
20220722-pg_dump: error: invalid number of parents 0 for table
Hi Team, We are using PostgreSQL 12.3, It is running under windows In that server there is nearly 8 databases are running. I have changed the data directory from one drive to another drive. At the time of change the data directory, have stopped the PostgreSQL instance and Copy the contents under the data directory and paste it on the new drive, and changed the data directory in the registry and start the instance. After that all the databases are working, but unable to take backup for one database. In 8 database, I have faced this issue in one database. When I see the log file it gives the following error, 2022-07-16 12:16:58.019 +04 [9332] STATEMENT: select f_rta_log_partition(); 2022-07-16 12:21:41.186 +04 [12936] LOG: request to flush past end of generated WAL; request D9F/E61D8E10, currpos D9F/E61AB138 2022-07-16 12:21:41.186 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:41.187 +04 [12936] ERROR: xlog flush request D9F/E61D8E10 is not satisfied --- flushed only to D9F/E61AB138 2022-07-16 12:21:41.187 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.458 +04 [12936] LOG: request to flush past end of generated WAL; request D9F/E61D8E10, currpos D9F/E61AB138 2022-07-16 12:21:42.458 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.459 +04 [12936] ERROR: xlog flush request D9F/E61D8E10 is not satisfied --- flushed only to D9F/E61AB138 2022-07-16 12:21:42.459 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.460 +04 [12936] WARNING: could not write block 9 of pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.460 +04 [12936] DETAIL: Multiple failures --- write error might be permanent. . When I try to back up by using command prompt it shows the below error pg_dump: error: invalid number of parents 0 for table "" suggest me how to solve this issue. Thanks in advance. Thanks, Karthick Ramu
Re: 20220722-pg_dump: error: invalid number of parents 0 for table
On 7/22/22 8:20 AM, Techsupport wrote: *Hi Team,* We are using PostgreSQL 12.3, It is running under windows In that server there is nearly 8 databases are running. I have changed the data directory from one drive to another drive. At the time of change the data directory, have stopped the PostgreSQL instance and Copy the contents under the data directory and paste it on the new drive, and changed the data directory in the registry and start the instance. After that all the databases are working, but unable to take backup for one database. In 8 database, I have faced this issue in one database. When I see the log file it gives the following error, */2022-07-16 12:21:42.460 +04 [12936] WARNING: could not write block 9 of pg_tblspc/907117/PG_12_201909212/907120/2611/* */2022-07-16 12:21:42.460 +04 [12936] DETAIL: Multiple failures --- write error might be permanent./* … When I try to back up by using command prompt it shows the below error *//* */pg_dump: error: invalid number of parents 0 for table "Table name>"/* suggest me how to solve this issue. Do you have table spaces other then the default set up? In particular for partitioned tables. Thanks in advance. *Thanks,* *Karthick Ramu* -- Adrian Klaver adrian.kla...@aklaver.com
Re: How does postgres sort large strings?
On Fri, 22 Jul 2022 at 16:46, Sergey Burladyan wrote:
> I thought that the sorting values are stored entirely in work_mem, but in
> fact it works somehow differently.
> Can anyone suggest how this works?
In the classic go by chunks way?
To sort values you need to compare them, to compare strings you do not
need the whole string, i.e. if you have to 1000 byte strings, one is
500A,500B, other is 1000A, to compare them ( using C locale, others
can be done in a similar way ) you can read 10 bytes from each and
compare, if they are the same, read 10 more, if they are not you are
done, if you hit the end of both strings, they are equal, if you hit
the end of one ( the shorter ), that one goes first. You can even do
it a character at a time. In the example, after looping 50 times on
10A you hit 10B, 10A, second string goes first, you do not even need
to look at the rest. A char at a time will end on the 501 char.
And probably PG can compare the strings in the shared buffers, so it
only needs some housekeeping information in work mem, and rely on its
infrastructure to bring the contents into shared buffers. I do not
think you are estimating memory usage right.
Francisco Olarte.
>
> For example, I created this 512MB incompressible file and test table:
>
> tr -dcs '[:print:]' '[:print:]' < /dev/urandom | tr -d '"'\' | dd bs=1K
> count=512K of=asciidump
>
> create unlogged table t1 (v text);
> insert into t1 select pg_read_file('asciidump') from generate_series(1, 10);
>
> select pg_column_size(v), octet_length(v) from t1 limit 1;
> pg_column_size | octet_length
> +--
> 536870912 |536870912
>
> set work_mem to '64MB';
>
> Now I think that 64MB is not enough to sort such large values and postgres
> will use temp files,
> but in fact it does not.
>
> select temp_files, temp_bytes from pg_stat_database where datname =
> current_catalog;
> temp_files | temp_bytes
> +
> 0 | 0
>
> explain (analyze,verbose,buffers) select v from t1 order by v;
> QUERY PLAN
> ---
> Sort (cost=94.38..97.78 rows=1360 width=32) (actual time=6433.138..6433.140
> rows=10 loops=1)
>Output: v
>Sort Key: t1.v
>Sort Method: quicksort Memory: 25kB
>Buffers: shared hit=543881 read=679794 written=118012
>-> Seq Scan on public.t1 (cost=0.00..23.60 rows=1360 width=32) (actual
> time=0.007..0.009 rows=10 loops=1)
> Output: v
> Buffers: shared hit=1
> Planning Time: 0.035 ms
> Execution Time: 6433.155 ms
>
> > Sort Method: quicksort Memory: 25kB
>
> select temp_files, temp_bytes from pg_stat_database where datname =
> current_catalog;
> temp_files | temp_bytes
> +
> 0 | 0
>
> WOW! How does it work?! :-)
>
> --
> Sergey Burladyan
>
>
RE: 20220722-pg_dump: error: invalid number of parents 0 for table
Thanks for your reply Adrian Klaver, Yes, I have the tablespace. It is not located in the default data directory. It is comes under new directory Thanks, Karthick Ramu -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Friday, July 22, 2022 9:23 PM To: Techsupport; pgsql-general@lists.postgresql.org Subject: Re: 20220722-pg_dump: error: invalid number of parents 0 for table On 7/22/22 8:20 AM, Techsupport wrote: > *Hi Team,* > > We are using PostgreSQL 12.3, It is running under windows > > In that server there is nearly 8 databases are running. > > I have changed the data directory from one drive to another drive. At > the time of change the data directory, > > have stopped the PostgreSQL instance and Copy the contents under the > data directory and paste it on the new drive, > > and changed the data directory in the registry and start the instance. > > After that all the databases are working, but unable to take backup > for one database. > > In 8 database, I have faced this issue in one database. > > When I see the log file it gives the following error, > > > */2022-07-16 12:21:42.460 +04 [12936] WARNING: could not write block > 9 of pg_tblspc/907117/PG_12_201909212/907120/2611/* > > */2022-07-16 12:21:42.460 +04 [12936] DETAIL: Multiple failures --- > write error might be permanent./* > > > > When I try to back up by using command prompt it shows the below error > > *//* > > */pg_dump: error: invalid number of parents 0 for table " Table name>"/* > > suggest me how to solve this issue. Do you have table spaces other then the default set up? In particular for partitioned tables. > > Thanks in advance. > > *Thanks,* > > *Karthick Ramu* > -- Adrian Klaver adrian.kla...@aklaver.com
