Re: Import csv to temp table

I use csv2table almost every day: https://github.com/f0rk/csv2table to just emit a create table statement: csv2table --file your_csv.csv pipe output to psql to create. easily used to import data as: csv2table --file your_csv.csv --copy --backslash -1 | psql your_database use arguments like --ti

Hi All,

Currently I'm working on upgrading a postgresql version from 9.3 to 12. The database size is around 700G. I finished the pg_upgrade process but when I ran 'analyze_new_cluster.sh' it stucked at the first log 'vacuumdb: processing database "otdb": Generating minimal optimizer statistics (1 target)'.

Re: Autovacuum on Partitioned Tables

that a correct statement?* -- It's good to know that the query optimizer will improve with partitions on versions 12+. Thank you. Best, Ryan Ryan N Ruenroeng (He/His) (717) 578-3929 | rruenro...@gmail.com | Madison, WI <https://maps.google.com/?q=Madison,%20WI> <https://github.c

Autovacuum on Partitioned Tables

ually track the statistics of these partitions and manually vacuum the tables or will autovacuum help to manage them? Best, Ryan Ryan N Ruenroeng (He/His) (717) 578-3929 | rruenro...@gmail.com | Madison, WI <https://maps.google.com/?q=Madison,%20WI> <https://github.com/rruenroeng>

PGSQL Phriday #001 - Two truths and a lie about PostgreSQL

tributing a post are outlined in the invite, but please feel free to reach out to me if you have any questions. https://www.softwareandbooz.com/pgsql-phriday-001-invite/ Regards, Ryan Booz

postgresql 9.6x installers for windows are no longer available

Does anyone know what happened to the EDB windows postgresql 9.6 installers? The host skips from 9.5->10 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Re: DBeaver does not show all tables in a Schema

In a recent update (not sure when), the default for DBeaver seems to have changed so that the navigator view is set to "simple", rather than "advanced" which shows all objects. Right-click the server -> edit connection -> Select "General" -> verify "Navigator View" On Fri, Oct 29, 2021 at 9:48

RE: WAL File Recovery on Standby Server Stops Before End of WAL Files

you both for your help. Sincerely, -Les -Original Message- From: Dilip Kumar Sent: October 27, 2021 10:29 PM To: Kyotaro Horiguchi Cc: Ryan, Les ; pgsql-generallists.postgresql.org Subject: Re: WAL File Recovery on Standby Server Stops Before End of WAL Files On Thu, Oct 28, 2021 at 7:2

WAL File Recovery on Standby Server Stops Before End of WAL Files

starting with 00010419005B but the restore process always stops at 00010419005A. I have two questions: * Why does the WAL file recovery process now stop after it reads 00010419005A? * What do I need to do to get PostgreSQL to recover the rest of

Re: Growth planning

As for clustering, unfortunately, it's a one-time operation in Postgres (as far as I'm aware), so you'd have to "cluster" the index every time after an insert or update of data. If it is partitioned, I presume it can be run on the index of each partition table individually - but I'm not sure. On M

Re: Faster distinct query?

Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions. This is what depesz is referring to: https://wiki.postgresql.org/wiki/Loose_indexscan On Thu, Sep 23, 2021 at 3:04 AM hubert depesz lubaczewski wrote: > On Wed, Sep 22, 2021 a

Re: Faster distinct query?

Index Cond: (station > NULL::text) Heap Fetches: 19 HTH, Ryan On Wed, Sep 22, 2021 at 6:22 PM Israel Brewster wrote: > On Sep 22, 2021, at 2:05 PM, Ryan Booz wrote: > > Ah. I didn't realize that. If SkipScan was chosen, you'd actually see it &g

Re: Faster distinct query?

gt; On Sep 22, 2021, at 1:50 PM, Ryan Booz wrote: > > Cool. I'd be interested to see the explain on it if you ever try it again. > On that cardinality, I'd expect it to be really fast, so I'm interested to > see if the (SkipScan) nodes were actually used. > > > Wit

Re: Faster distinct query?

Cool. I'd be interested to see the explain on it if you ever try it again. On that cardinality, I'd expect it to be really fast, so I'm interested to see if the (SkipScan) nodes were actually used. On Wed, Sep 22, 2021 at 5:35 PM Israel Brewster wrote: > > On Sep 22, 20

Re: Faster distinct query?

/ Anyway, it might be worth a shot. HTH Ryan B On Wed, Sep 22, 2021 at 4:27 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis wrote: > >> In the future, please share the plan returned by explain analyze, and >> some d

Hash partitioning, what function is used to compute the hash?

Regarding hash partitioning, what is the function/algorithm that is used to compute the hash for the partition key? I need to write a query like “SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records FROM existing_table GROUP BY 1” Sent from Mail

Re: migrating from Oracle to PostgreSQL 11

security fixes over 10.1. There is no good reason to start a new project on an outdated minor release with known security issues. https://why-upgrade.depesz.com/show?from=10.1&to=10.9&keywords= As Gavin asked, why not start on Pg11 with 11.4 being the current release? Ryan Lambert

Re: pg_dump and search_path

the view in the proper schema. PGOPTIONS='-c search_path=piws,public' sqitch deploy Ryan

Re: pg_dump and search_path

I had a similar problem and was able to being the command with the search_path to work around it. I did this on Linux and it looks like you are on Windows but I maybe you can do something similar that will work? PGOPTIONS='-c search_path=staging, transient, pg_catalog' *Ryan Lambert*

Re: Can you make a simple view non-updatable?

On Fri, Jun 8, 2018 at 8:27 AM, Adrian Klaver wrote: > > Using INSTEAD OF trigger?: > Yep, that's one way.

Re: Can you make a simple view non-updatable?

> maybe it is time to overhaul the security concept. > I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views. However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user acc

Can you make a simple view non-updatable?

;view". Thanks! Ryan

Re: Is there a way to create a functional index that tables tableoid column as an arg?

table called "person" that was in the first > schema listed in search_path, then the query would not do what you > want. You might want to consider prefixing the input parameter into > get_inherited_tables with the schema name too. > Good point. Thanks again! Ryan

Is there a way to create a functional index that tables tableoid column as an arg?

though - I'd like to index the is_a_kind_of() call. And Postgres supports functional indexes! So I try: create index animal_is_person on animal ( is_a_kind_of(tableoid::regclass, 'person') ); ERROR: index creation on system columns is not supported I see that this is because "tableoid" is a system column. Does anyone know any workaround for this? So close yet so far away! Thanks! Ryan

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

> > I believe the main, and maybe only, concern is the bloating of the system > catalog tables since you are constantly adding and removing records. Yes, > they will be vacuumed but vacuuming and bloat on catalog tables slows every > single query down to some, degree since every query has to looku

Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

.g. a plpgsql function that uses a temp table? What are the specific problems if I do this? Is the problem ameliorated if I add ON COMMIT DROP? Best, Ryan

Best way to select a random row from a derived table

subqueries Is there an option that is reasonably efficient and can be used on views and subqueries? Thanks! Ryan

Re: unsubscribe

es up with only the text you wrote in your email, Tom. I'm definitely not getting it... I thought maybe I had set these messages to forward through my work email account, but I just verified that there is no forwarding. I subscribed under the address at which I'm reading them. Ryan On

Re: unsubscribe

m_term=link> > <#m_5093462671357003734_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> > > On Mon, Nov 20, 2017 at 10:47 PM, Ryan wrote: > >> So my searches on the web all seemed to be somehow connecting me with the >> old majordomo2 system. I think that may have been the attempt th

Re: unsubscribe

with fewer things to do on a daily basis, I would and should have paid more careful attention. On Mon, Nov 20, 2017 at 10:42 PM, Danyelle Davis wrote: > have you tried the link provided in the previous email? > > On Mon, Nov 20, 2017 at 10:16 PM, Ryan wrote: > >> Hi, >> &

Re: unsubscribe

Hi, All right, so not for lack of trying, I cannot figure out how to unsubscribe. I've tried three different things, but they've either been ineffective or result in me getting an automatic email that the attempt failed. Kindly help? I enjoyed my daily digest of PostgreSQL messages, but this has b