>My postgres instance is based on docker image postgres:11 and runs on my
MacBook Pro i7 16GB.
How much ram and such did you give to this vm?
>To my surprise, postgres 11 is extremely slow when creating a full text
index. I added a column of tsvector type and tried to create an index on
that col
You can also look at citext type to avoid the casting.
customer_keyinteger DEFAULT
nextval('customer_key_serial') PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
namevarchar UNIQUE ,
Why do you have a surrogate primary key g
>I am trying to import some data from spreadsheets. Included in the data
>sets are US monetary values. These appear in the CSV file, like this: $1.00
>The column is defined like this: NUMERIC(5,2) NOT NULL.
1) remove all $ characters from csv before import
OR
2) import into text field (perhaps in
Just a side note, that the below can be written more simply-
CASE WHEN UserSkills.craftsmanship_id IS NULL THEN FALSE
ELSE TRUE as has
If you want to be direct-
UserSkills.craftsmanship_id IS NOT NULL as has
>
> Partition key: LIST (date_part('year'::text, mis_ora))
>
As an aside, you may benefit from switching to range partitioning*
depending on how your queries are written. If you have conditions such as
"WHERE mis_ora BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE" or similar, then
the fact that your p
> - For tables that receive only INSERTs, schedule a regular VACUUM
> with "cron" or similar. Unfortunately, PostgreSQL isn't very smart
> about vacuuming insert-only tables.
>
What is the need to vacuum on an insert only table? Does that just maintain
the freespace map?
>
> If the data is held in common tables(bills, vouchers, etc)then the only
> thing I see happening is changing the PK values to an unused value. That
> could turn into a nightmare though. Not only that you lose the connection
> to the original data source. If the data can be broken out into separa
>
> db=# select name from table_name_ds_tmp where ARRAY[categoryid] = ANY (
> select string_to_array( '200,400', ',')::bigint[] );
>
Using either of the below instead, I get the proper result. Why doesn't ANY
work? I do not know.
select name from table_name_ds_tmp where ARRAY[categoryid] <@ ( sel
me of this blog
post:
http://www.databasesoup.com/2015/02/running-with-scissors-mode.html
Note that sometimes I have run Postgres on a tmpfs as well to test
some specific patches. So that can be done, and of course that's
unsafe.
--
Michael
signature.asc
Description: PGP signature
Much of indexing strategy depends on knowing the data like how many
distinct values and what the distribution is like. Is JsonBField->>'status'
always set? Are those three values mentioned in this query common or rare?
Can you re-write this query to avoid using an OR in the where clause? Are
you ju
Both of the below visualizers can help, but require some knowledge about
comparing estimated vs actual row estimates, disk sorts vs in memory,
etc. Drawing implications about whether your schema needs to change or just
the query will take time to master as well.
http://tatiyants.com/pev/#/plans/ne
"I would like to convert a table with a primary key into a partitioned
setup by a column which is not part of the primary key"
That isn't possible. The partition key must be contained by the primary
key. That is, the primary key could be site_id, id and you can create hash
partition on id or site_
>
> My Json has always a status
>
Why declare the "where" clause when creating the index? It would not seem
needed if status is always set and so your index will reference all rows in
the table.
Thanks for sharing the trick of having the second column in the index
determine the key based on the f
>
> Hi
>>
>>By inserting data in a JSONB type column I got the following error
>> message:
>>
>> *>> *
>>
>> *ERROR: string too long to represent as jsonb string*
>>
>> *DETAIL: Due to an implementation restriction, jsonb strings cannot
>> exceed 268435455 bytes.*
>>
>> *<< *
>>
>> could anyo
No, what you want is not possible and probably won't ever be I would
expect. Scanning every partition to validate the primary key isn't scalable.
On Mon, Oct 7, 2019 at 5:56 PM Ron wrote:
> On 10/7/19 6:17 PM, Michael Lewis wrote:
> > No, what you want is not possible and probably won't ever be I would
> expect.
>
> Sure it is. Maybe not the (weird) way that Postgres does partitioning,
> but
> the legacy RD
On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan wrote:
> Hi Michael,
>
>
>
> In this case , I always need to include partition key(date) in primary
> key ( if I have a primary key defined on non partition key column e.g id
> (in my case), to make it a composite primary
s. I would recommend also
that you do not use a version which has known bugs.
--
Michael
signature.asc
Description: PGP signature
On Tue, Oct 15, 2019 at 8:25 AM Geoff Winkless wrote:
> On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell wrote:
> >
> > On 15/10/2019 14:28, stan wrote:
> > > I used to be able to return a constant value in a SELECT statement in
> > > ORACLE. I need to populate a table for testing, and I was going to
It sounds like you want row level security-
https://www.postgresql.org/docs/9.6/ddl-rowsecurity.html
But, you will need to define separate roles on the database and ensure that
the users and connecting with separate roles. The db can't magically know
about the permissions on the OS side.
>
"pg_rewind is a tool for synchronizing a PostgreSQL cluster with another
copy of the same cluster, after the clusters' timelines have diverged. A
typical scenario is to bring an old master server back online after
failover as a standby that follows the new master."
-https://www.postgresql.org/docs/
>
> We must know it to avoid disk out problems, and too much off-time.
>
You may be interested in this extension- https://github.com/reorg/pg_repack
Your plan to loop over tables and truncate them seems great if you are
worried. It seems simple to verify that space is being freed as you go, and
also easy to change tactics if the need arises.
>
>
> > CREATE TABLE books (
> > id SERIAL PRIMARY KEY,
> >
> > Which has the advantage of not having to manually create the sequences.
> Will
> > this also enforce that the "internally created sequence" will be
> initialized
> > to a value above the maximum key in use on a pg_restore?
>
>
> But it prints too many records: all moves cross-multiplied with each other.
>
> As if I have forgotten to add 1 more condition to the JOIN LATERAL
>
LIMIT 1 inside your lateral should resolve that. Personally, I'd move that
condition to EXISTS condition inside WHERE clause instead. Just a styl
On Thu, Oct 24, 2019 at 3:35 AM Laiszner Tamás
wrote:
> Actually, this is not such a unique idea:
> https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c
>
> Thanks for the suggestion to split up the primary key into components. But
> even going down this way, packing the compo
On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber
wrote:
> Thank you Michael -
>
> On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis wrote:
>
>> But it prints too many records: all moves cross-multiplied with each
>>> other.
>>>
>>> As if I have forgotte
erhaps you are just using a fork of Postgres..
--
Michael
signature.asc
Description: PGP signature
rustee" uuid NOT NULL,
> "AccessControlType" smallint NULL,
> "AceOrder" int NULL
> );
The original complain comes from a function called ComputeComputer.
What does it do and where does it come from? Does it call any in-core
code which would cause the failure. If t
On Fri, Nov 1, 2019 at 9:22 AM Shatamjeev Dewan wrote:
> Hi Michael,
>
>
>
> I want to create a partition by year and subpartition by month in postgres
> 11 timestamp column. Please advise syntax.
>
https://www.postgresql.org/docs/11/ddl-partitioning.html
The document
My thinking is opposite from what you have. I consider it important to very
aggressive on autovacuum because it only ever does the required amount of
work. If a tiny amount of work is needed, it does only that and is done.
Assuming it doesn't cause I/O concerns, do it as often as possible to
minimi
It seems that the definition of a view from pg_catalog.pg_views does not
qualify the tables used in the view if the tables are in the current search
path.
Is it possible to either have the definition always qualify all tables
independent of the search_path (or else provide a new column that does
t
Is that how PgAdmin does it? The views extracted by PgAdmin are fully
qualified
On Sun, Nov 3, 2019 at 3:15 PM Tom Lane wrote:
> Michael Shapiro writes:
> > It seems that the definition of a view from pg_catalog.pg_views does not
> > qualify the tables used in the view if
Can I set search_path='' (ie to a string that does not match any existing
schema)? Would that be the proper way to guarantee that the definition for
any view will always be fully-qualified?
On Sun, Nov 3, 2019 at 3:15 PM Tom Lane wrote:
> Michael Shapiro writes:
> >
You certainly could choose to store as tstzrange, but why not use two
fields?
https://www.postgresql.org/docs/current/rangetypes.html
> CREATE TYPE po_dates AS (
> po_isssued_datetimestamptz,
> discount_last_date timestamptz,
> net_date timestamptz
> );
>
What advantage does combining these three values into a custom composite
type give you rather than just storing directly?
tem
indexes, you also have the option of using ignore_system_indexes.
--
Michael
signature.asc
Description: PGP signature
> Considering that the auditing needs to be the last, how can I be sure it´ll
> ran lastly ?
>
IMHO, auditing should be done in after- triggers, when that the data in the
records being inserted, updated or deleted can't be changed but you can
still write to the separate auditing tables.
--
Mike No
ASE | SYSTEM } name
--
Michael
signature.asc
Description: PGP signature
mar more extensible for future purposes and it eases the option
parsing.
--
Michael
signature.asc
Description: PGP signature
r versions
of Postgres to be installed in parallel.
--
Michael
signature.asc
Description: PGP signature
On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan wrote:
> I am trying to create a foreign key constraint on a table : audit_param in
> postgres 12 which references partitioned table audit_p. is there anyway to
> get rid of this error.
>
>
>
*ERROR: there is no unique constraint matching given
ncern only the page for REINDEX. Perhaps this could
be improved, but I am not sure how and particularly if changing it is
worth it as many people are used to the existing way of presenting the
commands synopsis as well.
--
Michael
signature.asc
Description: PGP signature
On Thu, Nov 21, 2019 at 04:56 Jill Jade wrote:
> Hello everyone,
>
> I am new to Postgres and I have a query.
>
> I have updated a table which I should not have.
>
> Is there a way to extract the transactions from the WAL and get back the
> previous data?
>
> Is there a tool that can help to ge
ay? And what
can I do to make the planner pick up a better plan?
We are running PostgreSQL 10.10.
Sincerely,
-- Michael Korbakov
re simple of course :)
--
Michael
signature.asc
Description: PGP signature
On November 21, 2019 at 19:14:33, Pavel Stehule (pavel.steh...@gmail.com)
wrote:
čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov
napsal:
> Hi everybody.
>
> I stumbled upon a weird problem with the query planner. I have a query
> on a typical EAV schema:
>
> SELECT
I try to avoid DISTINCT and use GROUP BY when feasible, as well as avoiding
OR condition. If you combined anon1 and anon2 with UNION ALL, and did
(inner) join instead of left, or even moved all of that to EXISTS, perhaps
that gives you better consistent performance. Something like this-
SELECT co
hot_standby_delay, as told in
https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl
--
Michael
signature.asc
Description: PGP signature
On November 22, 2019 at 20:28:39, Michael Lewis (mle...@entrata.com) wrote:
I try to avoid DISTINCT and use GROUP BY when feasible, as well as avoiding
OR condition. If you combined anon1 and anon2 with UNION ALL, and did
(inner) join instead of left, or even moved all of that to EXISTS, perhaps
o use Jsonb NULL - "null"::jsonb"
>
> I don't know, but in this case, the exception should be verbose. This is
> "rich" function with lot of functionality
@Andrew: This patch is waiting on input from you for a couple of days
now.
--
Michael
signature.asc
Description: PGP signature
an, all
have implementations to handle the first type of incremental backup.
> Is there any alternative?
>
> Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup
Yeah, nothing has been done in uptream though in this area. That's
more about differential backups.
On Sun, Dec 1, 2019 at 8:09 AM Martin Gainty wrote:
>
> that said I think MS missed the boat on 2-phase-commits
>
Microsoft has never really embraced the concept of a multi-user database
environment. (It doesn't really understand the concept of a multi-user
operating system, either.)
--
Mike N
cale pg_dump
would be very good at. I would have personally avoided using pg_dump
above 10~20GB. Depending on the downtime you are ready to accept,
a migration based on Slony could be something to investigate.
--
Michael
signature.asc
Description: PGP signature
On Wed, Dec 04, 2019 at 08:38:01AM +0100, Thomas Kellerer wrote:
> But pg_upgrade only supports 8.4+
Ditto. You're right here.
--
Michael
signature.asc
Description: PGP signature
Postgres has to do it. FWIW, you
can track that using an even trigger for CREATE TABLE or other objects
which inserts the following in a table of your choice for a given
database:
- The timestamp of the transaction.
- The object name.
- Its class ID, say pg_class::regclass for a table, etc.
--
Michael
signature.asc
Description: PGP signature
and
then do the operation at once without the need to patch Postgres.
--
Michael
signature.asc
Description: PGP signature
On Fri, Dec 6, 2019, 3:21 AM Mladen Marinović
wrote:
> Is there a way to detect why the planing is taking this long?
>
> The database is a 9.6.1 with 32GB of shared_buffers, and 1GB of
> maintanance_work_mem, and machine CPU is below 80% all the time.
>
What is default_statistics_target set to f
I'd suggest re-writing your query to avoid ORs whenever possible. Is this
generated by an ORM or subject to change with filters selected in
application or can you totally control it on DB side?
It may be hugely more performant to simply rewrite this as (almost) the
same query twice UNION ALL'd tog
On Wed, Dec 11, 2019 at 1:54 PM Rich Shepard
wrote:
> A sampling location table has 28 distinct sites, each site being sampled
> from 1 to 67 times. I'm trying to obtain the number of sites having 1
> sample, 2 samples, ... 67 samples and am not seeing the solution despite
> several alternative q
It looks like you are creating a partition for each minute of the day (when
logs get inserted for a given minute at least). Would it be at all
reasonable to have an hourly or daily job which creates the partitions
ahead of when they are actually needed? If partitions went unused in the
recent past,
I don't recall the details, but I know v12 included significant
enhancements to lock relations later in the process such that when
targeting relatively few of the partitions, it can be a major performance
boost.
>
> LEFT join mfg_vendor_relationship on
> mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
> AND
> mfg_vendor_relationship.project_key = bom_item.project_key
> LEFT join vendor on
> mfg_vendor_relationship.vendor_key = vendor.vendor_key
>
Perhaps I am missing som
I would not think that behavior would extend to lock_timeout based on the
explanation on stackexchange. I would assume that the potentially long
runtime in this function is mostly in acquiring the lock and not doing the
update given the implied primary key in the where clause, so perhaps
lock_timeo
>
> I’m thinking it might be worth it to do a “quick” test on 1,000 or so
> records (or whatever number can run in a minute or so), watching the
> processor utilization as it runs. That should give me a better feel for
> where the bottlenecks may be, and how long the entire update process would
> t
On Mon, Jan 6, 2020 at 2:34 PM Mark Zellers
wrote:
> Just out of curiosity, what kind of trigger are you using, a row level
> trigger or a statement level trigger? If you are using a row level
> trigger, see if you can achieve your requirements using a statement level
> trigger instead. I’m rel
compiled by gcc (GCC)
> 4.9.3, 64-bit
I think that you had better report that directly to the maintainers of
the tool here:
https://github.com/reorg/pg_repack/
--
Michael
signature.asc
Description: PGP signature
On Wed, Jan 8, 2020 at 7:20 AM github kran wrote:
> Sorry for the confusion, I tried to install this extension on 9.6 and it
> seems to be not working. Does 9.6 PostGreSQL supports logical replication ?
>
No. See the top of this page with supported versions listed. Prior to v10,
pg_logical exte
aker-based stuff just to name two. These
rely on more complex architectures, where a third node is present to
monitor the others (any sane HA infra ought to do at least that to be
honest).
--
Michael
signature.asc
Description: PGP signature
tial data loss, and the amount of
data retention you are willing to have around. There is no way to
tell what's good or not for your product if you don't know that
first.
2) Read the documentation:
https://www.postgresql.org/docs/current/high-availability.html
--
Michael
signature.asc
Description: PGP signature
On Wed, Jan 8, 2020 at 8:52 PM github kran wrote:
> You are right on RDS but I believe the problem is on Aurora PostgreSQL
> where the pglogical throws an error during installation. Are you aware if
> this works on Aurora PostGreSQL
>
It seems like this question should be sent to AWS support fo
>
> when I manually mocking the data into both tables are fine and when I run
> the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion
> constraint matching on the CONFLICT specification
>
> the procedure is
>
...
> INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id,
>
primary to the original one.
Simple is easier to understand. Now the larger your instance, the
longer it takes to copy a base backup and the longer your reduce the
availability of your cluster. So be careful with what you choose.
--
Michael
signature.asc
Description: PGP signature
d with a package
different than the main ones for the server and client binaries and
libraries. Then, you would most likely look for a package named
postgresql-11-contrib or similar.
--
Michael
signature.asc
Description: PGP signature
"handle this aggregated data later in code"
What is your end goal though? Also, approx how many rows in these tables?
Can you share an example query and plan? What version are you using?
>
;t have a backup, but nothing more as there is no guarantee
that other parts of the system are not broken. As others have already
pointed out, take the time necessary to read that:
https://wiki.postgresql.org/wiki/Corruption
But first, you should take three, slow, deep breaths. Rushing can
only make things worse.
--
Michael
signature.asc
Description: PGP signature
On Wed, Jan 15, 2020 at 9:41 AM Werner Kuhnle wrote:
> I've tried to using the newer definition:
> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
> but that does not solve the problem.
>
> Is there a way to define id columns to that when the database provides
> values,
> it recognizes alread
On Thu, Jan 16, 2020 at 6:28 AM stan wrote:
> I am trying to create a function to automatically create a reference value
> when a record is inserted into a table. I want the reference value to
> consist of the user that is doing the insert, plus a couple of dates, plus
> a sequence number, where
backup would fix this, but it didn't.
Did you try to contact the maintainers? Here is the link to the
project:
https://github.com/pgbackrest/pgbackrest
--
Michael
signature.asc
Description: PGP signature
In terms of preventing this happening again, you might consider setting
some reasonable temp_file_limit as default. Alternatively or additionally,
you can set up another volume and direct temp files to use that to avoid
the server crashing if excessive temp files are used in a moment's time.
Then o
Other than creating a functional index on the keys that you most want to
access and include that function call in your query, I don't know that you
can. Functional indexes get statistics while partial indexes do not. If a
GIN index using path operator collected stats on the frequency of the keys,
t
If the new value does not overlap with any existing, allow. If it does
overlap, then it must be fully contained by the existing element, or the
existing element must be fully contained by it. Else, reject. Is that right?
27; when in an aborted
> transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
Thanks Vik for starting a new thread. For reference here is the
thread where the patch is being discussed:
https://www.postgresql.org/message-id/09502c40-cfe1
On Fri, Feb 7, 2020 at 6:29 AM Justin wrote:
> WorkMem is the biggest consumer of resources lets say its set to 5 megs
> per connection at 1000 connections that 5,000 megs that can be allocated.
>
Clarification- work_mem is used per operation (sort, hash, etc) and could
be many many times with
It may also be worth noting that it is possible to make autovacuum/analyze
more aggressive, perhaps only on the tables that see large changes in data
that might result in a statistics issue. If you could share a query,
explain analyze output, and pseudo code or at least description of what
sort of
rly on Windows
though.
--
Michael
signature.asc
Description: PGP signature
rted
> we'd need to generate all the .bc files we're generating on make based
> builds.
Oh, I see. That's indeed... Not straight-forward.
--
Michael
signature.asc
Description: PGP signature
There is more than one type of statistics though. Stats on the distribution
of data is easily recreated with analyze table_name or analyzing the whole
database. What about the stats on how many rows have been inserted or
updated since the last (auto)vacuum and that will be used to trigger
autovacuu
Given the explicit begin before drop/create, this is a bit of an unexpected
gotcha to have any impact on other transactions. Are there other known
limitations of DDL in a transaction?
I am very interested in this discussion. We settled a table with a single
timestamp field that a script updates every minute with NOW() so that we
can check the timestamp of that table on the replica, assuming the clocks
are synced, then we will be able to compute the lag.
>
> We have seen some deadlocks and tempfile count in pg_stat_database view.
> We are trying to reset the stats.
> Can we use pg_stat_reset() function to reset these stats without any
> impact stats of databases.
> Please advise the process to reset the stats.
>
Yes, you could reset stats every mo
work_mem can be used many times per connection given it is per sort, hash,
or other operations and as mentioned that can be multiplied if the query is
handled with parallel workers. I am guessing the server has 16GB memory
total given shared_buffers and effective_cache_size, and a more reasonable
w
Have you tried with JIT turned off in PG12? The long running node
is ProjectSet type which is related to set returning functions. If that is
getting evaluated differently in PG12, that may be issue.
On Tue, Mar 10, 2020 at 12:23:49PM +0530, Sonam Sharma wrote:
> We have pg_read_all_stats role from v9.6. do we have a similar role for
> v9.5 and lower versions ?
No, and pg_read_all_stats has been introduced in Postgres 10, not 9.6:
https://www.postgresql.org/docs/10/release-10.html
--
M
I don't know the answer to your stated question. I am curious if you have
set wal_level = minimal and if not, if that would be appropriate for your
use case and might render your concern a non-issue.
On Fri, Sep 20, 2019 at 8:19 AM Tom Lane wrote:
> There is a restriction on how many distinct GRANTs you can
> issue against any one object --- performance will get bad if the ACL
> list gets too large.
>
Any ballpark numbers here? Are we talking 50 or 8000?
A vacuum full rebuilds the tables, so yeah if it didn’t successfully
complete I would expect a lot of dead data.
On Fri, Mar 13, 2020 at 07:41 Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:
> We did a "vacuum full" on a database which had been interrupted by a
> network outage.
>
>
>
look at it. Anything ASCII-based should be of no problem.
If you have a doubt, reindexing evey index which includes text column
data is the best course of action in my opinion if you have any
doubts, because that's safe even if it has a higher cost.
--
Michael
signature.asc
Description: PGP signature
Do you have default fillfactor set on this table? If not, I would wonder if
reducing it to 50% or even 20% would allow many more HOT updates that would
reduce bloat.
Also, is there any period of lower activity on your system that you could
schedule a vacuum freeze for daily or weekly? I believe ha
work_type.work_type_key = work_type.work_type_key
You've done a cross join.
101 - 200 of 889 matches
Mail list logo