Re: Suggestion: provide a "TRUNCATE PARTITION" command

What is interesting here with the TRUNCATE WHERE (and in the proposed plpgsql) is to offer the end user a way to perform a transparent truncate or delete totally independent of the partitioning scheme (if any, or even if it has changed). -- Sent from: https://www.postgresql-archive.org/PostgreSQ

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Hello, maybe a naïve plpgsql as proposed in https://www.postgresql-archive.org/Partitionning-support-for-Truncate-Table-WHERE-td5933642.html may be an answer Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: simple reporting tools for postgres in aws

Hello, If you know Oracle, maybe you also know Sql developer reporting features, and as SQL developer can connect to Postgres, it can be used for free (it is what Oracle claims, but it should be double verified) https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r40/Chart/12cCha

Re: SV: Using Postgres jdbc driver with Oracle SQL Developer

Try Replacing hostname by hostname/Database? Don’t Forget ? Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_stat_sql_plans ALPHA released

A new version of pg_stat_sql_plans (version 0.2) has been released. Main changes are: - planid is build at planning time, making it reusable by cached plans, - expose current queryid, planid per pid in pg_stat_activity, - planning counters are now calculated per (queryid,planid), - includes a spec

Re: Migration from Oracle to Postgres

Jean Claude wrote > Hi guys, > > I hope you're doing well. > > Simple question > What are the best methods to migrate a huge table from Oracle to Postgres > ? > > Cheers Hello, Depending on the datatype, and if the databases are co-located on the same server, you may try oracle_fdw (except for

Re: pg_stat_statements : how to catch non successfully finished statements ?

Tom Lane-2 wrote > legrand legrand < > legrand_legrand@ > > writes: >> Tom Lane-2 wrote >>> The hard part here is that you have to be really careful what you do in >>> a PG_CATCH block, because the only thing you know for sure about the >>> backend

Re: pg_stat_statements : how to catch non successfully finished statements ?

Tom Lane-2 wrote > legrand legrand < > legrand_legrand@ > > writes: >> So the only solution is to had queryId to ErrorData in this hook >> or create a new hook fired on ERROR and containing queryId ? > > I see no particular need for a new hook. What's need

Re: Questions about Partitioned Tables and Indexes

Hi, what you proposed seems correct to me. I don't know how to list indexes from a partitionned index. You can check if your indexes are valid using: select i.relname as indexname,i.relkind, t.relname as tablename, t.relkind, idx.indisvalid from pg_class i join pg_index idx on idx.ind

Re: Partitioning an existing table - pg10.6

Hello, Trying to do what I suggested, I understood it doesn't work ;o( Logical replication can only work between two distinct databases, and it seems that the replicated table name is the same as its source ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-

Re: Partitioning an existing table - pg10.6

Hello, I didn’t test it myself but maybe using logical réplication could help ... See https://www.postgresql.org/docs/10/logical-replication.html Operations - create parttable - Feed it using réplication - when sync : stop app, stop réplication, rename tables - maybe you can réplicate from part

Re: Display View Columns and Their Source Tables and Columns

Hi, I don't know if it is possible ... the only way I found seems to use pg_depend and pg_rewrite as described here https://pgdba.org/post/2018/04/dependency_ladder/ Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

RE: pg_stat_statements doesn't track commit from pl/pgsql blocks

forgot to cc pgsql-general list De : legrand legrand Envoyé : vendredi 22 février 2019 20:26 À : Bruce Momjian Objet : RE: pg_stat_statements doesn't track commit from pl/pgsql blocks Hello Bruce, thank you for taking time to answer. yes, I was expe

pg_stat_statements doesn't track commit from pl/pgsql blocks

Hello, It seems that pgss doesn't track commit (nor rollback) commands from pl/pgsql blocks. using psql in version 11.1: select pg_stat_statements_reset(); do $$ begin commit; end $$; select calls,query from pg_stat_statements; ... I don't know how difficult it would be to fix it, but this coul

Re: oracle_fwd - is it safe or not?

lup wrote > On 1/31/19 12:48 PM, legrand legrand wrote: >> >> In all cases, developer support was premium. >> > Can you please expand on "support was premium".  I'm not sure if that > was the level of support purchased, or perhaps an indication that &g

Re: oracle_fwd - is it safe or not?

Hi, We have use it to "archive" 2 Oracle databases (8i and 9i) to pg 9.5 on windows (for a target of more than 250GB). We also use it to monitor our Oracle 11g databases, storing some performances / capacity planning data (like ASH, AWR, ...) in Postgres. In all cases, developer support was p

Re: Adding LEFT JOIN to a query has increased execution time 10 times

Alexander Farber wrote > Good evening, thank you for the useful hints! > > With the further help of the IRC folks the query has been optimized (7-10 > seconds -> 0.3 second) by adding the following indices: > > CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY')); > CREATE INDEX

Re: (Again) Column Store on PostGreSQL

Hi, VOPS, is one of the propotypes worked on see https://www.postgresql.org/message-id/4fb855c3-22b9-444f-21bf-114fa23cc...@postgrespro.ru https://github.com/postgrespro/vops Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Change from 9.6 to 11?

Hello, this seems as if some data was missing on a joined table ... could you compare the result of EXPLAIN ANALYZE for that statement between both databases ? and maybe share them ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_stat_statements: can we extend the object names to the qualified names?

A part of the answer would be to store explain (verbose on) select count(*) from t1; result in pg_stat_statements for the corresponding query... (Verbose On) gives the "qualified names": QUERY PLAN --- Aggregate (cost=19.38..19.39 rows=1 width=8) Output: c

Re: Syndicating PostgreSQL mailing list to Discourse

Hello, What you are proposing with https://rubytalk.org/ seems very interesting. It offers a quick view on mobile of "latests posts for all sites" in one click, and many other grouping /filtering options (that miss PostgreSQL website), for users that don't use fat client mailling list system (li

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

Ravi Krishna-9 wrote > Well your information needs some update. > > - On AIX, IBM had no issues selling Oracle, a rival to DB2. > - IBM Global Services, a consulting unit was the single biggest sales > force for Oracle Installations outside > Oracle. In other words, they ended up using Oracle fo

Re: editable spreadsheet style interface

Tim Clarke-2 wrote > On 30/10/2018 20:32, Martin Mueller wrote: >> >> I have used Aqua Data Studio for several years. Jetbrains recently >> released a similar product. Academic licensing is affordable (~ $200 a >> year) and very cheap if considered in terms of the time it saves you. >> >> *From:

Re: How to get partition info for a partition table?

see https://www.postgresql.org/docs/9.5/static/app-psql.html for psql options an commands: psql -d ... -U ... \d+ measurement_year_month ... Partition key: RANGE (date_part('year'::text, logdate), date_part('month'::text, logdate)) Number of partitions: 0 if you want to know how postgresql is r

Re: no queryId in post_parse_analyze hook when row is locked

Problem in pgsentinel has been identified, and should be fixed soon. it has nothing to do with post_parse_analyze hook. Sorry for the noise. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: [proposal] pg_stat_statements: extension timing instrumentation

An other way is to use "waits" reporting ... something like : pgss_store (...) pgstat_report_wait_start(PG_WAIT_EXTENSION); ... pgstat_report_wait_end(); gives waits of type "Extension", name "Extension" when spending time in this part of pgss. This can help those working

no queryId in post_parse_analyze hook when row is locked

Hello, When playing with extension pgsentinel, it seems that post_parse_analyze hook doesn't return query->queryId (initialized by a similar hook in pg_stat_statements) when current row is locked. Is that possible ? More details found at https://github.com/pgsentinel/pgsentinel/issues/19 Thanks

Re: survey: pg_stat_statements total_time and entry deallocation

Hello, What about adding a log message for each entry_dealloc() execution? it could be usefull to start thinking increasing pg_stat_statements.max. is there any rule regarding the acceptable max value ? I'm playing in test with a 20 000 value without any problem, could it extendend to 100 000 ?

[proposal] pg_stat_statements: extension timing instrumentation

Don't you have been surprised by the duration of a query (seen in psql with \timing) and the duration for the same query found in pgss ? It seems that writting the first query text to file takes some time, that high execution rate on the same query could generate waits on locks as when pgss is u

Re: pg_stat_statements in the query column I am not getting the exact query

Hi, This is the expected behavior, like that sql statements with différent values are shared in pg_stat_statements ... There is no parameter to change this, if you really want query paramèters you néed to have a look at statements logging, but be carrefull this can generate a huge volume of log fil

Re: Return select statement with sql case statement

Hello, sorry your description is not clear ... why do you use a GROUP BY on product without aggregation function min, max, sum ? where is defined numberOfPremiumDays ? may be using UNION can solve your problem: select numberOfPremiumDays, produc

Re: Help in Postgresql

Hello Moohanad, Did you check for pg_stat_statements https://www.postgresql.org/docs/10/static/pgstatstatements.html ? This is based on postgres hooks and will give you: db,user,query id, query text There are many developments trying to add start /end time, planid, plan text as described in http:

Re: pg_stat_statements : how to catch non successfully finished statements ?

Hello, Here is a patch that : - adds a new guc: pg_stat_statements.track_errors boolean (default to true), - capture of DML, DDL, PL/PGSQL commands in error into pgss. There is always a risk that new code used in PG_CATCH (mainly pgss_store) gives an error. I'm not able to tell when it could occ

Re: pg_stat_statements : how to catch non successfully finished statements ?

Progress report on this subject: 1/ Some clarifications: What is expected here is to update pgss counters for ALL the queries that have been executed, taking into account queries finished in SUCCESS and thoses finised with ERROR. Main interest here is to catch queries that are cancelled or inte

Re: pg_stat_statements : how to catch non successfully finished statements ?

OK no need of a new hook. I'll try to implement what you suggest here, but this clearly exceeds my (poor) development skill. As you also noticed, in addition to collect this SQL statement counters for the corresponding QueryId, I would have been interested in its PlanId ... but it's an other subj

Re: pg_stat_statements : how to catch non successfully finished statements ?

OK I see ... This hook should be used only for ERROR (no WARNING nor NOTICE ...) and the only real interesting information is ErrorData -> internalquery; /* text of internally-generated query */ This doesn't permit to (re)build the link to queryid (that is based on parse tree, but not availa

pg_stat_statements : how to catch non successfully finished statements ?

Hello all, I was wondering if there is a hook to collect non successfully finished SQL statements in pg_stat_statements (timed-out, cancelled, killed, or simply errored) ? Thanks in advance Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Postgres Foreign Data Wrapper and DB2 LUW

Hello, not sure that there is a dedicated fdw for DB2 LUW, but you may try one of ODBC_FDW (and maybe JDBC_FDW) as decribed here: https://wiki.postgresql.org/wiki/Foreign_data_wrappers Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pg_stat_statements: password in command is not obfuscated

Hello, It seems that passwords used in commands are not removed when caught by pg_stat_statements (they are not "normalized" being utility statements) exemple: alter role tt with password '123'; select query from public.pg_stat_statements where query like '%password%'; query --

Re: wrong message when trying to create an already existing index

> regression=# create index mv1 on t1 (f1); ... > ERROR: materialized view "mv1" already exists Is in fact the one I prefer ;^) I come from a DBMS world where Tables and Indexes do not share the same name space, and have to change my mind ! Thanks you Tom for pointing that. Regards PAscal

Re: wrong message when trying to create an already existing index

OK, that noted ! thank you for the quick answers Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: wrong message when trying to create an already existing index

I thougth that thoses messages where using relation's relkind: r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table wouldn't it be easier to read for beginners ? Regard

wrong message when trying to create an already existing index

Hello, When trying to create an already existing index (in pg 9.5) SQL> create index if not exists NEWINDEX on SCHEMA.TABLE(COL); > relation "NEWINDEX" already exists, skipping message speaks about relation (and not index) Would it be possible that this message reports the correct object

Re: Bulk Insert/Update Scenario

Hi, check documentation Populate a database this explains how to create a dummy table, load it using COPY command, and then INSERT / UPDATE target tables (using ON CONFLICT if needed) You can also investigate: - file_fdw

Re: Does the delete+insert is fater and less cpu consumer than update?

If you use partitioned tables, and just want to reload data from some partitions you can then use the truncate partition syntax or develop a procedure like described here https://www.postgresql.org/message-id/am4pr03mb171327323dcd2069a532756190...@am4pr03mb1713.eurprd03.prod.outlook.com Regards

Re: Partition pruning / agg push down for star schema in pg v11

Partition pruning doen't work in this case as described at http://www.postgresql-archive.org/Partition-pruning-for-Star-Schema-td5995163.html#a5995168 Partition wise join works for ONLY ONE dim table (that is better than nothing). -- Sent from: http://www.postgresql-archive.org/PostgreSQL-gener

Re: Partition pruning / agg push down for star schema in pg v11

Adding partitioning on the dim tables, with the same keys as those used in the fact table, gives any star schema a good chance to use Partition Wise Join / Aggregate plans. Will test it soon Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: PostgreSQL and Data Warehouse optimizations

Hi, Parallel Queries are now available and development is very active for Declarative Partitioning (arrived with pg 10), many new features are currently developped in pg 11, (for exemple hash partition, partition wise ... most regarding partition usage optimizations) see https://commitfest.postgre

Partition pruning / agg push down for star schema in pg v11

Hello, Working on Oracle migration POCs, I'm very interested in v11 and declarative partitioning optimizations. I have a typical star schema, having dimension tables "product", "calendar" and "country" and a fact table "sales". This fact table is partitionned by time (range by month) and country

Re: seq vs index scan in join query

Hi, Could you give us the partitions (ranges values) and indexes definition for result table ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: declarative partition by list, "other values" syntax

Found it in pg 11devel: CREATE TABLE wiki_data_part_a PARTITION OF wiki_data_part DEFAULT; -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: One Partition by list is always chosen by planner

Hello, thank you for this answer. I just wanted to add a few informations. This table has in fact around 20 partitions, explain select * from wiki_data_part where category='fr' returns only 2 partitions (meaning that constraint_exclusion works) the partition that is always scanned has more tha

Re: One Partition by list is always chosen by planner

yes it is show constraint_exclusion partition and if I explain the same query with an other filter explain select * from wiki_data_part where category='fr' | Append (cost=0.00..14010.76 rows=291609 width=48)

One Partition by list is always chosen by planner

Hello, after creating a table wiki_data_part with partition by list (category); and creating partitions like CREATE TABLE wiki_data_part_a PARTITION OF wiki_data_part FOR VALUES IN ('ang.q',...,'arc'); CREATE TABLE wiki_data_part_b PARTITION OF wiki_data_part FOR VALUES IN ('bs.s',...,'bg.n'); c

declarative partition by list, "other values" syntax

Hello, I was trying to create a partitionned table by list, that has on special partition for "other values" for exemple create table wiki_data_part( category varchar(20), tim_id bigint, pag_id bigint, requests int, size bigint ) PARTITION BY LIST (category ); CREATE TABLE wiki_d