[GENERAL] pg_dump: could not format inet value

Hi, I've stumbled on pg_dumpall problem: -- pg_dump: ERROR: could not format inet value: Address family not supported by protocol pg_dump: SQL command to dump the contents of table "elog" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: could not format inet value: Addre

Re: [GENERAL] pg_dump: could not format inet value

I'm sorry, it was issue with my PITR replication setup which I think I've fixed now. (race condition between full db backup and pg_dump). Regards, Tomas Simonaitis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will i

Re: [GENERAL] cannot connect to server

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Srikanth, hae a look at this thread: Most probably the cause on your computer is different, but you can get some ideas about where to find log files and how

Re: [GENERAL] Doubt in Backup

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Mar 21, 2011 at 11:43:57AM +0530, Adarsh Sharma wrote: > Dear all, > > 2 days ago, I need to backup 2 databases in my Database server because I > need to format the system and reinstalls again with the back up databases. > > After a fresh inst

Re: [GENERAL] searchable database

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, Apr 09, 2011 at 12:10:46PM +0530, quickinfo wrote: > Dear Friends, > > I need help from you. > > We have more than thousand electronic journals. I want to make a searchable > database for easy access. Is there any light wight database availab

[GENERAL] Different cast behavior of TEXT and VARCHAR

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, When PREPARing statements, the type guessing machinery seems to behave differently for VARCHAR and TEXT. Is this intentional? This is all against PostgreSQL 8.4.5 Illustration: | tomas@floh:~$ psql foo => psql (8.4.5) => Type &quo

Re: [GENERAL] Different cast behavior of TEXT and VARCHAR

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Apr 12, 2011 at 10:29:44AM -0400, Tom Lane wrote: > to...@tuxteam.de writes: > > When PREPARing statements, the type guessing machinery seems to behave > > differently for VARCHAR and TEXT. Is this intentional? > > Your example works for me, i

Re: [GENERAL] Need suggestion

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Jun 01, 2011 at 10:08:14AM +0200, Carl von Clausewitz wrote: > Hello Everyone, > > I got a new project, with 100 user in Europe. In this case, I need to handle > production and sales processes an its documentations in PostgreSQL with PHP. Som

Re: [GENERAL] Need suggestion

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Following up on myself: Just stumbled upon this in the hackers mailing list, which might be interesting to you, since it highlights pros & cons of current implementations: Re

Re: [GENERAL] SQLite-PostgreSQL comparison

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, Jun 05, 2011 at 05:17:30PM +0200, Andre Majorel wrote: > Does anyone know of a document comparing SQLite and PostgreSQL ? Just google around a bit:

Re: [GENERAL] timezone help?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Jul 19, 2011 at 12:15:54PM -0700, Adrian Klaver wrote: > On Tuesday, July 19, 2011 12:01:19 pm David Salisbury wrote: > > I'm a bit new to PG, and having troubles with timestamps. The docs list: [...] > > My immediate problem is below.. > >

Re: [GENERAL] Another unexpected behaviour

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Jul 18, 2011 at 03:18:43PM -0700, Shianmiin wrote: > setup: > > drop table if exists t1; > create table t1 (f1 int); > create unique index uix_t1 on t1(f1) ; > insert into t1(f1) values (1), (2), (3); > select * from t1; > > f1 > --- > 1

Re: [GENERAL] [HACKERS] Debian no longer dumps cores?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Oct 20, 2008 at 05:49:04PM -0300, Alvaro Herrera wrote: > Hi, > > My Debian system (now running Linux 2.6.26) is no longer dumping core > files, and I can't figure out why :-( FWIW, same happens here, out-of-the-box 2.6.26-1 vanilla Debian. B

Re: [GENERAL] [HACKERS] Debian no longer dumps cores?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Oct 20, 2008 at 05:49:04PM -0300, Alvaro Herrera wrote: > Hi, > > My Debian system (now running Linux 2.6.26) is no longer dumping core > files, and I can't figure out why :-( Tested now with 2.6.25-2. Coredumps still work there. I submitted

Re: [HACKERS] [GENERAL] Index greater than 8k

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Oct 31, 2006 at 08:55:04PM -0800, Joshua D. Drake wrote: > > >> We are not storing bytea [...] [...] > > Hmm, have you tried to create a functional trigram index on the > > equivalent of "strings(bytea_column)" or something like that? Hrm. So

Re: [HACKERS] [GENERAL] Index greater than 8k

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Nov 01, 2006 at 07:16:37PM -0300, Alvaro Herrera wrote: > [EMAIL PROTECTED] wrote: [...] > > a "functional trigram index"? (this would be very cool). > > Heh :-) I meant an index, using the pg_trgm opclass (which indexes > trigrams; hence the

Re: [GENERAL] Benchmarking partitioning triggers and rules

ting the data directly into the right partition (without any triggers). -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] Benchmarking partitioning triggers and rules

CUTE '...') is simply more expensive, as it needs to do more stuff (on every execution). There are reasons for that, but you may think of it as regular queries vs. prepared statements. Prepared statements are parsed and planned once, regular query needs to be parsed and planned over and ov

Re: [GENERAL] Slow query with join

1 FROM f_intervenant_ref WHERE (actor_id = ir_actor_id) AND (ir_dos_id = '5226')); or even an explicit join SELECT v_actor.* FROM v_actor JOIN f_intervenant_ref ON (actor_id = ir_actor_id) WHERE ir_dos_id = '5226')

Re: [GENERAL] DB on mSATA SSD

rather terrible idea. In case of crash you won't be able to even start the database, because it will try to recover the tablespace. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgs

Re: [GENERAL] DB on mSATA SSD

power-loss protection (which you need for a database, although a UPS may help a bit here). But maybe that changed recently. If you're on Linux use smartctl. Also, as others have pointed out 8.4 is out of support, so consider upgrading. +1 to this -- Tomas Vondra http:

Re: [GENERAL] DB on mSATA SSD

equent and tuning the kernel/mount options may help a lot. Putting the DB into RAM and backing up periodically to disk is a validsolutions? Well, that depends on your requirements. You may lose the changes since the last backup. Or, is storing indexes on a ram drive possible? No, not really.

Re: R: [GENERAL] DB on mSATA SSD

On 04/23/15 15:36, Job wrote: Hello, thank you first of all for your wonder help! Tomas, regarding: There are ways to make the writes less frequent, both at the database and OS level. We don't know what's your PostgreSQL config, but making the checkpoints less frequent and tuning

Re: [GENERAL] BDR Across Distributed Nodes

to 10.0.0.1. So something like this: # from 10.0.0.1 psql -h 10.0.0.2 -p 5432 demo # from 10.0.0.2 psql -h 10.0.0.1 -p 5432 demo If that does not work, you probably need to investigate - firewall settings, pg_hba.conf and postgresql.conf (maybe it's not listening on this address)? regards

Re: [GENERAL] Block Corruption issue..

rror, please immediately notify the sender at Cyient and delete the original message. Sure ... -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] Planner cost adjustments

tting random_page_cost = 2 (instead of default 4). Sadly, the original post provides very little information about the context, e.g. database size, storage system, explain plans etc. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remot

Re: [GENERAL] Planner cost adjustments

Hi, On 05/29/15 22:56, Daniel Begin wrote: Omg! I was not expecting such a step-by-step procedure, thanks! I'll follow the guide :-) Since I was about to provide a bit of context as asked by Tomas, here it is for those who are interested... Best regards, Daniel A bit of the required co

Re: [GENERAL] Help me recovery databases.

ses without /base? I have archive_status folder. -- С Уважением,Генералов Юрий -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] advocating LTS release and feature-train release cycles

with this topic. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_start_backup does not actually allow for consistent, file-level backup

pg_start_backup takes care of that. If you don't like that, you have multiple options - stop the database while performing the backup, perform file system level backup (e.g. lvm snapshot) or use tools like pg_basebackup. regards -- Tomas Vondra http://www.2ndQuadran

Re: [GENERAL] Turn off streaming replication - leaving Master running

data directory, remove the recovery conf and start it again as a standalone database? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.o

Re: [GENERAL] Forced external sort?

entations of the data, and the on-disk is much more compact. It's not uncommon to see 1:3 ratio, i.e. when external (on-disk) sort needs 100MB, the in-memory sort would need 300MB. So when the on-disk sort needs 1253824kB, you'll probably need ~4GB work_mem to actually do that in mem

Re: [GENERAL] Postgresql 9.4 and ZFS?

not as tightly integrated with the kernel, but it should release the memory when needed and such. Perhaps not letting it to use all the RAM is a good idea, but 1/3 seems a bit too aggressive? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, R

Re: [GENERAL] Postgresql 9.4 and ZFS?

ecently done on SSD, EXT4 / XFS are ~2x faster than ZFS. But of course, if the ZFS features are interesting for you, maybe it's a reasonable price. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Servi

Re: [GENERAL] Postgresql 9.4 and ZFS?

On 09/30/2015 07:33 PM, Benjamin Smith wrote: On Wednesday, September 30, 2015 02:22:31 PM Tomas Vondra wrote: I think this really depends on the workload - if you have a lot of random writes, CoW filesystems will perform significantly worse than e.g. EXT4 or XFS, even on SSD. I'

Re: [GENERAL] Postgresql 9.4 and ZFS?

On 09/30/2015 03:45 PM, Patric Bechtel wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Tomas, Tomas Vondra schrieb am 30.09.2015 um 14:01: Hi, ... I've also done a few runs with compression, but that reduces the performance a bit (understandably). I'm somewhat su

Re: [GENERAL] RAID and SSD configuration question

#x27;Always Write Back' allows the controller to buffer writes beyond what the devices do. AFAIK there's no difference. It's an option that disables write cache in case the battery on BBU dies for some reason (so the write cache would become volatile). With capacitors this is n

Re: [GENERAL] RAID and SSD configuration question

vulnerable to "write hole" problem. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] Avoid sorting when doing an array_agg

for free, especially with arrays of ids. regards -- Tomas Vondra  http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Strange activity of prepared queries

of the time the planning is fast enough not to be logged, so it's invisible for pgbadger. regards --  Tomas Vondra  http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Tomas Vondra  http://www.2ndQuadra

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

; Have you looked at the Postgres log entries that cover these > episodes? > > Is there anything of interest there? > In particular look at checkpoints. In the config file you've changed checkpoint_timeout, but you haven't changed max_wal_size, so my guess is the checkpoints ha

Re: [GENERAL] Special index for "like"-based query

s also possible to use simple btree index with varchar_pattern_ops. https://www.postgresql.org/docs/9.6/static/indexes-opclass.html regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent

Re: [GENERAL] Special index for "like"-based query

On 12/30/2016 12:46 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra mailto:tomas.von...@2ndquadrant.com>>wrote: On 12/30/2016 12:33 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:21 PM, Job mailto:j...@colliniconsulting.it>

Re: [GENERAL] Index impact on update?

ng the long update in this case - there may be other bits slowing it down - e.g. foreign keys checks, triggers. CREATE TABLE AS SELECT would not pay any of those costs, of course. Also, if you're running with wal_level=minimal, it would not have to write the changes into WAL, while the regu

Re: [GENERAL] Performance degradation when using auto_explain

is whether you actually need the timing - the total duration + row counts are far more important in my experience, so by setting auto_explain.log_analyze = on auto_explain.log_timing = off you may significantly reduce the impact on query performance, while retaining the most valuable

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/07/2017 04:43 AM, Venkata B Nagothi wrote: On Sat, Jan 7, 2017 at 2:56 AM, Job > wrote: __ Hi guys, really much appreciated your replies. >> You might want to include the query plans for each server W e use a function, the explain

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

it does not call gettimeofday() and so the clock source overhead is pretty much irrelevant. Moreover it's marked as 'stable' which makes repeated calls unnecessary. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remo

Re: [GENERAL] Slow index scan - Pgsql 9.2

but you'll have to include all columns into the index, even those in the index predicate: CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode) WHERE NOT is_demo AND NOT deleted; I'd bet all of those will outperform the current plan. regards -- Tomas Vondra

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

ext, and we reset it on RESET ALL. > In short, I agree with Guyren, there are features needed here that we don't have and it would be a great deal better if we did. Yeah. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DB

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

ecution time. Both planning time limits (reject based on estimates) and runtime (abort query if limit is reached). That might be an interesting feature, but it's also significantly more complex than the topic of implementing a safe context for secrets, making RLS less problematic with connection p

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

send the results to the mailing list: select name, setting from pg_settings where source != 'default'; That'll tell us how was the configuration modified. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

_id = a.master_user_id) while the 9.6 plan does not include anything like that, i.e. there's only a single aggregate at the top level, without any group keys. Also, the SQL query you've provided does not include any GROUP BY clause, so I claim that those plans are from two different qu

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

. Of course, there are other things we might need to know. For example if there are foreign keys between the tables, 9.6 is using that for the estimates (unlike 9.5). And so on. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote D

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

#x27;ll only consider group aggregate, because that can work with very little memory. At execution time we'll only use as much memory as actually needed. The trouble is that if we under-estimated the amount of memory, there's no way back. regards -- Tomas Vondra

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

-- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] using hstore to store documents

at you're looking for though, you'll probably need to explain what you're trying to achieve to get better answers. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-gen

Re: [GENERAL] full_page_writes on SSD?

one containing a lot of synchronous, single threaded, reads. Even with SSDs that can very quickly lead to not being able to keep up with replay anymore. I don't immediately see why that would happen? Can you elaborate? regards -- Tomas Vondra http://www.2ndQuadrant.com Po

Re: [GENERAL] full_page_writes on SSD?

s being handed over to the SSD. Of course, there might be other benefits of further lowering page sizes at the OS/database level (and AFAIK there are SSD drives that use pages smaller than 4kB). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Supp

[GENERAL] Table queue (locking)

; LOCK TABLE events IN SHARE MODE NOWAIT; -- block writers } SELECT * FROM events COMMIT; Intuitively I believe backing-off with NOWAIT is better (since readers performance is not that important). Could You suggest better ways to solve this problem?, maybe I'm missing something obviu

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

return number of tuples / occupied pages in the table and index. Tomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] any way for ORDER BY x to imply NULLS FIRST in 8.3?

Reece Hart <[EMAIL PROTECTED]> writes: However, it's not clear that you've considered a clause like 'ORDER BY (foo IS NULL), foo', which I believe is not implementation dependent. Yeah, that should work reasonably portably ... where "portable" means "equally lousy performance in every implement

Re: [GENERAL] INSERT performance deteriorates quickly during a large import

d it and haven't checked the following paragraphs ... Tomas ---(end of broadcast)--- TIP 6: explain analyze is your friend

[GENERAL] strange row count estimates with conditions on multiple column

ng 1% selectivity. But no matter what I do, I'm not sure how to combine these two estimates into 0.0001% (1 row out of a million). And I do have exactly the same problem with the estimate in (D). Where the heck did 227232 come from? regards Tomas -- Sent via pgsql-general mailing list (

Re: [GENERAL] strange row count estimates with conditions on multiple column

OK, thanks for the explanation. Cases (A), (B) and (D) are clear now. But I'm not sure about (C) ... Dne 17.11.2010 04:03, Tom Lane napsal(a): > Tomas Vondra writes: >> I'm not quite sure why (C) has an estimate of 1. > > It's smart enough to see that each of the

Re: [GENERAL] strange row count estimates with conditions on multiple column

Dne 17.11.2010 05:22, Tom Lane napsal(a): > Tomas Vondra writes: >> Estimate for condition >>... WHERE (col_a BETWEEN 33 AND 33) >> is about 10k rows, which is quite precise. On the other side estimate >> for condition >>... WHERE (col_b BETWEEN 33 A

Re: [GENERAL] strange row count estimates with conditions on multiple column

defined in selfuncs.h as DEFAULT_RANGE_INEQ_SEL, equal 0.005. Which makes sense, as it's related to the table size. I've mixed that with some other default which used to be 1000 (table size I think). regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] where is pg_stat_activity (and others) in the documentation?

s the background writer has started timed checkpoints (because the checkpoint_timeout time has expired) Which is a bit more detailed that the description of the view. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to identify whether the stats were reset?

like this solution and it does not detect resets of statistics for a single table, function or a bgwriter :-( But is there a better way? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to identify whether the stats were reset?

Dne 19.11.2010 05:56, Tom Lane napsal(a): > Tomas Vondra writes: >> We need to identify whether the stats were reset between the snapshots, >> because then the collected data are useless (and the scripts that do the >> analysis are quite confused). > > The stats ar

Re: [GENERAL] strange row count estimates with conditions on multiple column

know the default value is 100, just thinking about how to improve the estimates. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can the query planner create indexes?

> I see in another thread you suggest merely placing hints in the log. > That's a fine > first step - but I'll then be wanting to auto-parse that log to > auto-create Well, I don't think that is going to happen. I'm not going to implement this (I've explained my opinions on this above), and I don't think this would get into core anyway. Logging? Probably. Advisors? Maybe, although as a contrib module. Creating indexes automatically? I doubt that. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can the query planner create indexes?

Dne 21.12.2010 20:03, Jeremy Harris napsal(a): > On 2010-12-21 18:50, Tomas Vondra wrote: >>> Then the index you just built gets automatically dropped, as I said >>> above. >> >> I'm a bit confused. Should the indexes be dropped automatically (as you >>

Re: [GENERAL] database is growing... 1GB per day basis

t does, etc. Especially those beginning with 'vacuum' and 'autovacuum' - what values are set for those parameters? Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to generate unique invoice numbers foreach day

. using a table with one row for sequence (but that might be a bottleneck with multiple users and a lot of invoices). Tomas Dne 15.1.2011 20:32, Jorge Godoy napsal(a): > Why would you do that? > > You can always reset the sequence at the end of the day. > > -- > Jorge Go

Re: [GENERAL] How to generate unique invoice numbers foreach day

eck if there's a lot of invoices created at the same time by different users (or if the transaction is long). Tomas Dne 15.1.2011 21:04, Andrus Moor napsal(a): > Invoices can entered also some days forward or back. Users enters > invoice date and expected program to generate next s

Re: [GENERAL] Need help writing exclusion constraint

ect count(*) into c from event where (destination_id = dest) and ((starts, ends) overlaps (s,e)); return c = 0; END; $_$ LANGUAGE plpgsql; Or something like that. If there's no suitable table, you can use advisory locks - just replace the P

Re: [GENERAL] How to generate unique invoice numbers for each day

l get a lot of sequences (and it's a bit messy), and there will be gaps (not sure if this is acceptable). Or he can use the solution proposed in the General Bits 130 (which is basically the solution I've already proposed) and maintain gapless sequences using a table. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to generate unique invoice numbers for each day

, but there's nothing like that in PostgreSQL. So if you don't want to use the approach proposed in General Bits 130 (the one with gapless sequences implemented using a table), the only option I'm aware of is to create one sequence for each day. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

user_id from instr_as_stutemp) >>> >>> >>> >>> (table instr_as_stutemp has just one column and only 4 rows) >>> >>> >>> >>> What new feature of Postgres 8.4 would be making the query run so much more >>> sl

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

20110307}'::text[]))) I.e. both verions use seqscan, both estimate the same number of rows (incorrectly), yet the estimated cost is very different (9492043 vs. 548966). Maybe the cost estimation really changed between 8.0 and 8.4, but just for sure - what are the cost values? I mean what is s

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 22:31, Pavel Stehule napsal(a): > 2011/3/16 Tomas Vondra : >> Dne 16.3.2011 21:38, Davenport, Julie napsal(a): >>> OK, I did the explain analyze on both sides (using a file for output >>> instead) and used the tool you suggested. >>> >>>

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

change is valid in that session only, the other sessions will still use 1MB). Most of the sorts was performed on-disk insted of in memory, and it might result in better plan. regards Tomas Dne 16.3.2011 22:40, Davenport, Julie napsal(a): > Tomas, > Here are the settings on the 8.

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 17.3.2011 19:29, Davenport, Julie napsal(a): > I have not yet had time to try Tomas' suggestion of bumping up the work_mem > first (trying to figure out how to do that from within a coldfusion script). > Many thanks for all your help guys! Well, just execute this 'SQL q

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

Dne 18.3.2011 16:42, Davenport, Julie napsal(a): > This helped, is now down from 14.9 min to 10.9 min to run the entire script. > Thanks. Still, it's way slower than the 8.0 :-( regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

ts in the log) 2) increase wal_buffers (just set it to 16MB and forget it) The effect depends on the amount of data loaded and other things, but it's worth a try. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] a question about row estimation in postgres

I've noticed you're using the exact cardinality (1302). The planner does not have this exact information, it has to work with the value stored in pg_class.reltuples - what does this query return? select reltuples from rankings where relname = 'rankings' I guess it w

Re: [GENERAL] postgres conferences missing videos?

a decent DV cam and a microphone, it's quite easy. After all, it's just a pearson standing and talking, not a LOTR ... Hosting - unless you want something special, you can use youtube. We're quite happy with it. Tomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] postgres conferences missing videos?

Dne 21.3.2011 21:45, David Fetter napsal(a): > Who proposes, volunteers! How are you going to help? Well, if this is the only problem then I happily volunteer to take care of this for pgday 2011. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] postgres conferences missing videos?

sionals volunteer their time, they still have expenses like > travel, storage media, etc., and you can help defray those. Yes, there are travel expenses and it takes time. Storage media is not a big deal - you can use a simple HDD. regards Tomas -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] postgres conferences missing videos?

ides (and that's all we have usually) are not a complete information - it's merely a synopsis of the talk. Without the speech, it's often unusable. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] wal_level=minimal produces more data than archive level

ssible explanation I've noticed when reading the docs is that while wal_level=minimal allows to skip logging of some bulk operations (e.g. COPY, which is exactly what pgbench does when initializing the DB), it may need to log more data later (when actually running the bench). Is that a correct conclusion, or am I missing something? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] wal_level=minimal produces more data than archive level

Dne 3.4.2011 02:45, Tom Lane napsal(a): > Tomas Vondra writes: >> I've been playing with various wal_level settings, mainly to find out >> what is the overhead of hot standby, and I've noticed a strange thing. >> In some cases the wal_level=minimal produces signi

Re: [GENERAL] Replication: slave is in permanent startup 'recovery'

keep_segments config on the master, right? Right. If there are enough WAL segments on the master, the standby may ask for them and the data will be streamed to the standby. So the archive mode is not a requirement, although if you already use WAL archiving, it's a good idea to use it (no addit

Re: [GENERAL] question about memory usage

ng so many locks). You really need to check this (notice how the amount of shared memory depends on max_locks_per_transaction): http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS and this (which explains what max_locks_per_transaction does): http://www.d

Re: [GENERAL] Performance problem on 2 PG versions on same query

45.28 rows=2694 width=279) (actual time=13.230..555.366 rows=121063 loops=1) This may be one of the reasons why the database decided to use different join method. Are there any differences in settings between the two machines (e.g. work_mem)? regards Tomas -- Sent via pgsql-general mailing list

Re: [GENERAL] Performance problem on 2 PG versions on same query

Dne 5 Listopad 2014, 18:10, Tom Lane napsal(a): > "Tomas Vondra" writes: >> Dne 5 Listopad 2014, 17:31, R??my-Christophe Schermesser napsal(a): >>> We have 2 instances of PG, one in 9.1.1 and 9.1.14. They have the same >>> data, schema, PG configuration, an

Re: [GENERAL] invalid memory alloc request size

It's available at https://github.com/tvondra/pg_check and I just pushed some minor fixes to make it 9.3-compatible. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to analyze a slowdown in 9.3.5?

basic description first. Is it querying the database? Is it inserting or updating large amounts of data? Or is it something more complex? Aside from that, some basic system stats would be really helpful, so that we can identify the bottleneck (is that a CPU, I/O, locking, ...). A few lines from thes

Re: [GENERAL] Fwd: Data corruption after restarting replica

Filesystem is ext4 mounted with nobarrier > option. Good ;-) > Database is fairly large ~120GB with several 50mil+ tables, lots of > indexes and FK constraints. It is mostly queried, > updates/inserts/deletes are only several rows/s. So it was queried for a few hours? Any idea if

Re: [GENERAL] newbie how to access the information scheme

nformation_schema' > AND n.nspname !~ '^pg_toast' > AND pg_catalog.pg_table_is_visible(c.oid) > ORDER BY 1,2; > > I found the above by using the command: psql -E Or just use the information_schema like this: select table_schema, table_name from informatio

  1   2   3   4   >