Re: [GENERAL] Is it OK to create a directory in PGDATA dir

store our stuff > there, or will PG freak out seeing a foreign object. > PostgreSQL certainly does not check if there are unknown directories in the data directory, and it will not crash and burn. But it causes all sorts of problems, and it increases the probability of human error. re

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

On 10/11/2017 02:26 AM, pinker wrote: > Tomas Vondra-4 wrote >> I'm probably a bit dumb (after all, it's 1AM over here), but can you >> explain the CPU chart? I'd understand percentages (say, 75% CPU used) >> but what do the seconds / fractions mean? E.

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

On 10/11/2017 12:28 AM, pinker wrote: > Tomas Vondra-4 wrote >> What is "CPU load"? Perhaps you mean "load average"? > > Yes, I wasn't exact: I mean system cpu usage, it can be seen here - it's the > graph from yesterday's failure (after 6p.m

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

You may also make the bgwriter more aggressive - that won't really improve the hit ratio, it will only make enough room for the backends. But I don't quite see how this could cause the severe problems you have, as I assume this is kinda regular behavior on that system. Hard to say witho

Re: [GENERAL] Function to return per-column counts?

On 09/28/2017 04:34 PM, Seamus Abshere wrote: > hey, > > Does anybody have a function lying around (preferably pl/pgsql) that > takes a table name and returns coverage counts? > What is "coverage count"? cheers -- Tomas Vondra http://www.2

Re: [GENERAL] Speed of conversion from int to bigint

d RENAME TO table_id; COMMIT; You may need to tweak this to handle the inheritance tree. -- 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] shared_buffers smaller than max_wal_size

f expiration period, so a single page may end up being written to disk repeatedly. With sufficiently large shared buffers the page would be repeatedly modified in shared buffers, and written our just once by the checkpoint process. regards -- Tomas Vondra http://www.2ndQuadran

Re: [GENERAL] shared_buffers smaller than max_wal_size

disk so WAL can be deleted? > The last step in a checkpoin is fsync() on the files. Without that, the checkpoint is considered incomplete and the database won't rely on it. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support,

Re: [GENERAL] shared_buffers smaller than max_wal_size

the database will receive writes, etc. So there's nothing inherently wrong with (shared_buffers > max_wal_size) or (shared_buffers > max_wal_size), it depends on your workload. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support

Re: [GENERAL] random row from a subset

On 09/20/2017 01:28 PM, bluefrog wrote: > > thanks, interestingly your method works in both Oracle and PostgreSQL, > albeit with a different random function call. > It does not work in SQL Anywhere though. > You will have to ask SQL Anywhere people, I guess. cheers -

Re: [GENERAL] random row from a subset

r by c_d_id) as rn , count(*) over() max_rn from customer where c_d_id=5 ) t where rn = (select floor(r*(max_rn))+1 from rand); or define an immutable wrapper for random(): CREATE FUNCTION random_stable() RETURNS DOUBLE PRECISION AS 'SELECT random()' LANG

Re: [GENERAL] looking for a globally unique row ID

s of partitions), and thus overhead. It's a bit foolish to say you would live with the performance hit when you don't know how serious would it be. It may be fine but it may also be much worse than you expected. I ran into a number of people who used global indexes on other DBs, and

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 04/28/2017 01:34 AM, Andres Freund wrote: On 2017-04-28 01:29:14 +0200, Tomas Vondra wrote: I can confirm this observation. I bought the Intel 750 NVMe SSD last year, the device has 1GB DDR3 cache on it (power-loss protected), can do ~1GB/s of sustained O_DIRECT sequential writes. But when

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

it (power-loss protected), can do ~1GB/s of sustained O_DIRECT sequential writes. But when running pgbench, I can't push more than ~300MB/s of WAL to it, no matter what I do because of WALWriteLock. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

you'll have such a busy system, you probably do some research and testing first, before choosing the database. If we don't perform well enough, you pick something else. Which removes the data point. Obviously, there are systems that start small and get busier and busier over time. And

Re: [GENERAL] Unexpected WAL-archive restore behaviour

ww.postgresql.org/docs/9.6/static/continuous-archiving.html [2] https://wiki.postgresql.org/wiki/Warm_Standby regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list

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] 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] 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] 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] 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

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

[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] 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

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] 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] 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] 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] 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] 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] 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] 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] 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] 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] Is it possible to recover the schema from the raw files?

Hello Adrian, > Then I am of no further use to this conversation. No problem at all. Thank you for your well considered input and ideas. Have a lovely day. Kindest regards, Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

Re: [GENERAL] Is it possible to recover the schema from the raw files?

dest 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] Is it possible to recover the schema from the raw files?

r tools in order > to make some of the schema dumpable. This is actually a very interesting idea. Kind 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] Is it possible to recover the schema from the raw files?

d to cover the system information then I am > not sure how it would be possible to recover information from 0? This is an incorrect assumption. Most of the information is available. Just the PostgreSQL server can't work with it [because of the missing bits]. Kind regards, Tomas --

Re: [GENERAL] Is it possible to recover the schema from the raw files?

out. All this relevant information has already been supplied previously. Kind 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] Is it possible to recover the schema from the raw files?

information is *largely* intact - yet the small missing amount is causing PostgreSQL not being able to work with this particular database. Kind regards, Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.o

Re: [GENERAL] Is it possible to recover the schema from the raw files?

uestion potentially targets someone who could tell whether something like this is possible. Kind 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] Is it possible to recover the schema from the raw files?

, 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] Is it possible to recover the schema from the raw files?

Please note that I mentioned previously that the database is corrupt. "pg_class" table can be queried but it is not possible to dump the database in question as some of the pages in blocks are missing. In this case, it is necessary to rely on raw files only. Thanks. T -- Sent via pgsql-gener

Re: [GENERAL] Is it possible to recover the schema from the raw files?

d, is that the case? Yes. The database server's files were not damaged. > What happened if you connected to another database in the cluster? That's irrelevant. That said, some were damaged and some not. Kind regards, Tomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Is it possible to recover the schema from the raw files?

Hello Venkata, Thank you for your reply. You are stating the obvious though. If those conditions were met, I would have formulated my question differently. Kind regards, Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Is it possible to recover the schema from the raw files?

important in this case. Thank you in advance for any insight into such a topic. Kind 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] 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

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] 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] 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] 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] 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?

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?

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] 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] 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] 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] 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] 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] 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] 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] 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] 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: 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] 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: [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

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] 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] 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] 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] : :Full text search query ::

WHERE part_number_a = '104' Another option is to use an index with a 'varchar_pattern_ops' opclass, which allows you to do prefix LIKE queries [1] CREATE INDEX custom_partnum_idx ON partdetails (part_number varchar_pattern_ops); SELECT ... FROM partdetails

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

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] 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] 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] 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] 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] 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] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

somehow limits the options. So when you use LATERAL in the query, it may or may not be able to rewrite it to the better plan. To really understand what's going on here we need to see the explain plans of the queries. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

it just sitting there idle, doing nothing? Or is there some other process doing a lot of CPU (e.g. Java)? Can you try running the query through 'psql' directly, to rule out JDBC issues etc.? Try to collect explain plans for the query (maybe there's something wrong with it). 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] aggregate returning anyarray and 'cannot determine result data type'

tion of using anyarray instead of the internal structure does not work because I have no way to pass the other parameters to the final function (the last int in sfunc). Any ideas how to solve this? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Arduino SQL Connector

this might be sufficient. Excessive number of connections, because if you keep one connection from each arduino device, and you have 1000s of devices ... you get the idea. But this might be resolved using pgbouncer + transaction pooling or so. regards Tomas -- Sent via pgsql-

Re: [GENERAL] Arduino SQL Connector

t; > Just my 2c (if you disregard, please just ignore me :-) ), Arduino is a tiny computer, with a just a few kBs of memory. That's hardly sufficient for a Java environment (especially if even the libpq library is considered too large). regards Tomas -- Sent via pgsql-general mail

Re: [GENERAL] Arduino SQL Connector

imple authentication (trust, plaintext, md5) and simple queries (insert as a single string), it's pretty trivial. [1] http://www.postgresql.org/docs/9.1/static/protocol.html [2] http://www.postgresql.org/docs/9.1/static/protocol-flow.html#AEN91596 Feel free to ask if you run into some issu

Re: [GENERAL] Arduino SQL Connector

ted in being a TL of anything. I was messing with the PostgreSQL protocol [1] recently, and writing a code implementing something like what the mysql connector does would be quite trivial. A day of work, maybe two if you know networking but not the protocol. regards Tomas [1] http://www.postg

Re: [GENERAL] Arduino SQL Connector

lease-1.0.2-beta > > you will see they are doing the equivalent, using the MySQL libmysqlclient: > > http://dev.mysql.com/downloads/connector/c/ Really? I see no reference to libmysqlclient there. It seems they're simply reimplementing only a (small) subset of the library. 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] encrypting data stored in PostgreSQL

AES-128, IIRC). Somewhere in 3.x (or maybe very late 2.6.x) kcryptd was improved to use multiple threads - that's a significant improvement, both for throughput and latencies. Clearly, it's going to eat (part of) your CPUs, but that's expected. The encryption still has impact on laten

Re: [GENERAL] Linux OOM-Killer

oes it say? When you look at "top" output, which processes consume most memory? Are there multiple backends consuming a lot of memory? What queries are they running? Assuming you have a monitoring system in place, collecting memory stats (you should have that), what does it say about history? Is there a sudden increase in consumed memory or something suspicious? 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] cannot delete corrupted rows after DB corruption: tuple concurrently updated

ggers - maybe yes, but I'm not aware of any RI trigger doing updates. That being said, I think that what you're doing is wrong. If you think you have a corrupted database, I'd strongly suggest doing dump/restore. Or how do you know there's no other corruption lurking in the files

Re: [GENERAL] Question about memory usage

een using it. Chances are the old machine had swap, overcommit and/or higher swappiness, so it was not running into these issues with overcommit. Anyway, I see you've mentioned shmmax/shmall in one of your previous messages. I'm pretty sure that's irrelevant to the

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

gt; starting from 9.2, this behavior changed dramatically? I'm not sure I understand what you're trying to say. It's true the documentation does not give perfectly clear instructions on how to set optimal shared_buffers. The reason is very simple - there's no such value, optimal for all workl

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

that's translated to plain malloc). With sysctl.conf misconfigured, the database would not start at all and you'd get a reasonable error message in the log, suggesting pretty clearly where/how to fix it. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

ome sort of limit, imposed by the operating system. Please check ulimit and overcommit settings. * BTW the SO post you mentioned as a perfect match was talking about query executed over dblink - are you doing the same? If so, try to move the aggregation into the query (so that the aggregation happens

Re: [GENERAL] Tuning 9.3 for 32 GB RAM

You do have 32GB of RAM in total, so 22GB for caches seems about right unless you're running other memory-intensive applications on the same box (making less memory to be available for the filesystem cache). 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] Clang 3.3 Analyzer Results

k somewhere else first' is meant for regular questions that are not that closely related to postgresql development, and are likely to be answered in the generic mailing lists. Please, upload the HTML report somewhere and post a link. If it's easier to the clang analysis, maybe post instru

Re: [GENERAL] Replication and fsync

r a crash (be it due to OS error, power outage, ...). If the primary crashes for any reasons, you have to start from scratch, otherwise there might be silent corruption as you've described. 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] Increasing CPU usage of PostgreSQL

problem is most likely the "sync" at the end of the transaction. Rahila, if you want to saturate the CPU and don't care about the particular benchmark, try to use read-only transactions. Either just add "-S" at the pgbench command line, or write something SELECT-only o

Re: [GENERAL] Need help how to manage a couple of daily DB copies.

aily cron script that lists all databases on the test server, parses the database name and drops those older than 5 days. 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] Is there something like a limited superuser to give to a db-assistant?

rg/docs/9.3/static/sql-grant.html You don't need superuser privileges to create schemas and so on, and by definition "superuser" means "unrestricted". Just create a regular user and grant him CREATE on the database. You may also grant him access only to selected schemas/ta

Re: [GENERAL] Need some help on Performance 9.0.4

Please, don't send screenshots to the conference. The explain.depesz.com gives you a unique URL for each submitted query, so just copy it into the message. Like this: http://explain.depesz.com/s/SLj Anyway, now the query took only 5 seconds, not 10 as before. If you run the query with

  1   2   3   4   >