Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

have been told that the limit on Windows is about 300 concurrent users. I have no idea how accurate that statement is. I share your surprise because PostgreSQL is has a much more extensive feature set than MySQL. -- .Bill. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

[GENERAL] ADO/ODBC returns TEXT fields as Varchar, not LongVarChar

LongVarchar? -- .Bill. -- 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] ADO/ODBC returns TEXT fields as Varchar, not LongVarChar

? -- .Bill. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Text field truncated using ADO/ODBC?

suggests that the driver sees both Text and Varchar as unknown types. What must I do to get the ODBC driver to see the Text column as LongVarchar and the Varchar column as Varchar? The complete connection string follows by signature. Thanks. Bill Provider=MSDASQL.1;Extended Properties="D

[GENERAL] Trigger function is not called

test table with a before insert trigger and it works perfectly. I am new to PostgreSQL so I suspect I am missing something simple but I cannot figure out what. Why is the trigger function never called? Thanks, Bill CREATE TABLE note.category ( category_id note.d_id NOT NULL, category note.d_ca

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: PostgreSQL 8.3 on Windows. I have the table below which has a before insert trigger. The CREATE TRIGGER statement and the trigger function are also shown below. The script you show attempts to create the trigger before creati

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: In a newsgroup posting someone suggested that constraint checks on domains occur before the before insert trigger. Yeah, that is the case, but if a domain check was failing then the row wouldn't get inserted, so I'm

Re: [GENERAL] Trigger function is not called

roblem with the concept now that I understand it. It is just different than InterBase and Firebird which I have done a lot of work with lately. Thanks very much for your help. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: Is it possible to create a type and use that instead of the domain or will I have the same problem with a type? You'd have the same problem. By the time the trigger sees it, the row has already been converted to the tab

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: I removed the domain from the category_id and version columns leaving the following table, trigger function and trigger. The trigger function is still not called when I insert a new row. Any other ideas? You're still ex

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: The thing that has me confused is that the following table, trigger and trigger function work perfectly and the primary key for this table is also bigint not null. Actually, after looking closer, I think the problem with your pr

[GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...

STARTING WITH ? which is equivalent to LIKE 'ABC%' and will use an index on AFIELD. Is there a similar syntax in PostgreSQL? Bill -- 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] MySQL LAST_INSERT_ID() to Postgres

at currval() returns the last value obtained by calling nextval() for the current connection. Can someone confirm that currval() returns the the value for the connection from which it is called? Bill

Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...

Tino Wildenhain wrote: Hi Bill, Bill wrote: The SQL database servers I have worked with cannot use and index for a SELECT of the form SELECT * FROM ATABLE WHERE AFIELD LIKE ? because there is no way to know the location of the wild card until the parameter value is known. InterBase and

Re: [GENERAL] MySQL LAST_INSERT_ID() to Postgres

n after insert trigger and the after insert trigger inserts a row into another table which has a serial primary key. In that case I assume that lastval() will return the value from the serial column in the second table. Bill -- 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] MySQL LAST_INSERT_ID() to Postgres

Scott Marlowe wrote: On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote: I am new to PostgreSQL but it seems to me that lastval() will only work if the insert does not produce side effects that call nextval(). Consider the case where a row is inserted into a table that

[GENERAL] Clay Shirky observation regarding MySQL

Hello! Clay Shirky made a comment about MySQL that I thought the PostgreSQL community should be aware of: http://www.shirky.com/writings/situated_software.html It's the section (mostly toward the bottom) entitled, "The Nature of Programming, and the Curious Case of MySQL". The whole article

[GENERAL] Scalability

er as the number of user increases? -- .Bill. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] Scalability

"Joshua D. Drake" wrote: > Bill wrote: > > I am new to PostgreSQL and just beginning to learn the product. I > > will probrobably be using it exclusively on Windows. > > > > I was surprised to learn that PostgreSQL creates a new process for > > each

[GENERAL] How much memory is required?

the right place. Thanks. -- .Bill. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[GENERAL] Does PostgreSQL support multi-instancing?

Is is possible to have two different versions of PostgreSQL running on the same computer at the same time? -- .Bill. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Performance monitoring

Can someone point me to information about performance monitoring in the PostgreSQL documentation? I want to see what tools are available to diagnose performance problems. Thanks. -- .Bill. ---(end of broadcast)--- TIP 9: In versions below 8.0, the

Re: [GENERAL] How much memory is required?

"Raymond O'Donnell" wrote: > On 8 Nov 2006 at 22:48, Bill wrote: > > > Is there any published information on the minimum or recommended > > amount of memory for PostgreSQL on Windows and/or Linux. I am > > looking > > There's some useful inf

Re: [GENERAL] Performance monitoring

Jeff Davis wrote: > On Wed, 2006-11-08 at 23:37 +0000, Bill wrote: > > Can someone point me to information about performance monitoring in > > the PostgreSQL documentation? I want to see what tools are > > available to diagnose performance problems. Thanks. > > >

[GENERAL] Can PostgreSQL notify a client that a trigger has fired?

Does PostgreSQL have built in mechanism I can use to conditionally notify a client application that a trigger has fired? What I want is something along the line of the following pseudo code in a trigger. if then raise client event -- .Bill. ---(end of broadcast

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

mailing lists; you can get help with specific questions from experienced users. I appreciate all the anwers I have received here. They have made it possible for me to do a much better job in the time available. My thanks to everyone. -- .Bill. ---(end of broadcast)---

Re: [GENERAL] shared_buffers formula

s - whereas when you specify it > > using a size unit (like MB, GB,...) that amount of memory is divided by > > the size of a page. So you're off by a factor of 8192. > > > > Greetings, > > > > Andres Freund > > > > -- > > Andres Freund http://www.2ndQuadrant.com/ > > PostgreSQL Development, 24x7 Support, Training & Services > > -- Bill Moran -- 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] Weight BLOB objects in postgreSQL? How?

tore a bunch of it, then check the resultant size of the tables on disk vs. the actual size of the data. That's really the only way to know since the actual efficiency of data storage depends a lot on the data itself. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] #PERSONAL# Reg: date going as 01/01/0001

ng to execute the query, and the query itself. -- Bill Moran -- 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] #PERSONAL# Reg: date going as 01/01/0001

On Fri, 6 Mar 2015 15:24:28 +0530 Medhavi Mahansaria wrote: > Hi Bill, > > Here are the details of the table and the query i want to insert. > > > aml_db=> \d+ check_date > Table "public.check_date" > Column |

Re: [GENERAL] Postgres and data warehouses

icate using Slony instead of streaming, which allows you to create additional tables on the replica that are read/write in addition to triggers that only fire on the replica. It's complicated, but pretty damn powerful. -- Bill Moran -- 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] VACUUM FULL doesn't reduce table size

and see if the rows are sill nonremovable. I bet you $5.34 that everything works fine after that, which would indicate that the folks who made the snapshot didn't do it correctly. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL-related legal question

ntacting $random_legal_service. -- Bill Moran -- 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] PostgreSQL-related legal question

he situation on the list. That's why I'm just looking for a lawyer who understands the situation and can advise me. > > On Wed, Mar 11, 2015 at 1:28 PM, Bill Moran > wrote: > > > > > I've been asked to sign a legal document related to a PostgreSQL- >

[GENERAL] Postgresql BDR(Bi-Directional Replication) Latency Monitoring

l BDR? Regards, Bill

Re: [GENERAL] Autovacuum query

a tuples as the data in the table changes. It's not unusal for the table to be 2x the size of the actual data on a heavily updated table. -- Bill Moran -- 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] Column does not exists?

8 > > But if I do: > > DELETE From sessions WHERE "SESSIONTIMESTAMP" < '2010-01-01 10:02:02' > > It DOES work. > > Why the db doesn't recognize the name of the table without quotes?. See: http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS -- Bill Moran -- 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] Would like to know how analyze works technically

) was terminated by > signal 9: Killed > 2015-04-01 06:24:38 EDT DETAIL: Failed process was running: analyze verbose; > 2015-04-01 06:24:38 EDT LOG: terminating any other active server processes > > I started this process at 11PM, so it ran for about 7.5 hours before > crashing.

Re: [GENERAL] Would like to know how analyze works technically

On Wed, 1 Apr 2015 06:26:36 -0700 (MST) TonyS wrote: > On Wed, April 1, 2015 8:48 am, Bill Moran [via PostgreSQL] wrote: > > > > >>>> Running "analyze verbose;" and watching top, the system starts out > >>>> using no swap data and about 4GB

Re: [GENERAL] Would like to know how analyze works technically

r your memory problems. I'd suggest to set it > > to 16MB, and see if you can avoid "on disk" sorting. If not - gradually > > increase work_mem. > > > > Regards, > > > > > > Igor Neyman > > > > > Thanks Igor, > > I will tr

Re: [GENERAL] Serializable transaction restart/re-execute

r of tries). Is this > possible without going into pgsql source code? I suspect that savepoints will accomplish what you want: http://www.postgresql.org/docs/9.4/static/sql-savepoint.html -- Bill Moran -- 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] Serializable transaction restart/re-execute

On Mon, 6 Apr 2015 10:41:25 +0100 Filipe Pina wrote: > Hi Bill, thanks for the quick reply. > > I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and > should use BEGIN/END blocks and EXCEPTIONs. > > Did you check the URL I mentioned?

Re: [GENERAL] Regarding bytea column in Posgresql

ror and the data won't be accepted. The same thing happens if you try to store invalid XML in an XML field (such as XML without proper closing tags, etc). It seems that this strictness causes a lot of people to avoid those data types, as there seem to be a lot of people who would rather have garb

Re: [GENERAL] plpgsql functions organisation

irely possible I missed a memo, so I am > > open to a more detailed explanation of the inefficiencies involved. > > > > The Postgres source is written in C, not in plpgsql. C has a good > optimizing compiler and plpgsql doesn't. Maybe that's a roundabout way of

Re: [GENERAL] plpgsql functions organisation

ell enough. There are certainly cases when you want to create very complex logic in the database and plpgsql is liable to make that difficult. But there are a lot of cases where having to manage pointers and a build environment and all the things that go with C aren't justified, because plpgsql

Re: [GENERAL] Postgresql and GlassFish - cannot commit when autoCommit is enabled

tand how the connection pool works. You could confirm this by turning on full query logging in Postgres and see which connection does what. If it turns out to be the case, then you'll have sort out how your code is getting confused. If it's not the case, then I don't have any oth

[GENERAL] RPM building tools and info missing?

x27;t find any information on why that sit is down or where it might have gone to. Is this a temporary outage? Or has the RPM data moved somewhere else and isn't documented yet? Any help is appreciated. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

[GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

ke a lot of other database systems support it, and even though it's not ANSI, it's pretty much the de-facto standard. -- Bill -- 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] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Thu, 21 May 2015 13:57:24 -0400 Tom Lane wrote: > Bill Moran writes: > > My other question: is there a specific reason why PostgreSQL doesn't support > > this syntax, aside from "nobody has bothered to add such support"? Because > > I'm considering w

Re: [GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Fri, 22 May 2015 11:02:47 -0400 Tom Lane wrote: > Alban Hertroys writes: > > On 22 May 2015 at 04:46, Bill Moran wrote: > >> With all that being said, if I were to build a patch, would it be likely > >> to be accepted into core? > > > Wouldn't yo

Re: [GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Fri, 22 May 2015 11:27:49 -0500 Dennis Jenkins wrote: > On Fri, May 22, 2015 at 10:02 AM, Tom Lane wrote: > > > Alban Hertroys writes: > > > On 22 May 2015 at 04:46, Bill Moran wrote: > > >> With all that being said, if I were to build a patch, would i

Re: [GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On Fri, 22 May 2015 12:44:40 -0400 Tom Lane wrote: > Bill Moran writes: > > Tom Lane wrote: > >> Other questions you'd have to think about: what is the data type of > >> 0x; what do you do with 0x (too big > >> even f

Re: [GENERAL] FW: Constraint exclusion in partitions

s=51340 width=66)" > "Index Cond: (group_id = subset.id)" > Total query runtime: 3986 ms. 5978 rows retrieved. > > > select * from newtable where group_id IN (select * from subset) > "Hash Join (cost=41.25..138092255.85 rows=1935067087 width=66)" > " Hash Cond: (newtable.group_id = subset.id)" > " -> Append (cost=0.00..84877869.72 rows=3870134173 width=66)" > "-> Seq Scan on newtable (cost=0.00..0.00 rows=1 width=66)" > "-> Seq Scan on newtable_01 (cost=0.00..946235.96 rows=46526896 > width=66)" > ... > "-> Seq Scan on newtable_86 (cost=0.00..986527.64 rows=44269664 > width=66)" > " -> Hash (cost=38.75..38.75 rows=200 width=8)" > "-> HashAggregate (cost=36.75..38.75 rows=200 width=8)" > " -> Seq Scan on subset (cost=0.00..31.40 rows=2140 width=8)" > Execution Cancelled after 766702 ms ! > > I tried the same with "SET enable_seqscan = OFF" and got an index scan of all > tables; > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Bill Moran -- 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] FW: Constraint exclusion in partitions

On Sat, 23 May 2015 18:16:43 -0400 Daniel Begin wrote: > Hello Bill, > You wrote that my testing methodology is flawed - I hope you are right! > > However, I am a bit confused about your comments. Yes, I did edited the name > of the tables for clarity but if I miss the point

Re: [GENERAL] Efficient sorting the results of a join, without denormalization

ms > > Execution time: 1.390 ms > > (15 rows) > > > > regards, tom lane > > Wow, sorry I screwed up the query. It should be: > > ORDER BY c.created_at DESC > > Not b, or as you noted its trivial to index. Sorry! Creating an index on c.created_at sped things up by a factor of over 1000, which caused the case you defined to run in ~0.5ms for me. -- Bill Moran -- 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] Planner cost adjustments

> but follows a power law (some user_id would return millions of records while > others only one). > This is the farthest I can go at this point. Maybe someone can provide me > with more explanations regarding planner's behavior and ways to go further > to make it work properl

Re: [GENERAL] Automatic Client routing

Db is a failover manager which relies on virtual IP > management, not the one I described above. pgpool has this capacity. -- Bill Moran -- 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] Momentary Delay

b, gis_iti_itinerario iti_sub > where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem >and via_sub.via_status_viagem = 'A' >and via_sub.via_dt_hora_ini > now() - interval '9 hours' >and iti_sub.lin_cod_linha = 389 >and iti_sub.iti

Re: [GENERAL] Planner cost adjustments

hould I increase the statistic target to 500, or even > to 1000? > Is there something else I can trigger to get the appropriate plan? > > Comments/explanations would be appreciated > Daniel > > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [m

Re: [GENERAL] Momentary Delay

Please do not remove the mailing list from replies. See below. On Fri, 12 Jun 2015 09:21:19 -0300 Anderson Valadares wrote: > 2015-06-08 20:33 GMT-03:00 Bill Moran : > > > On Mon, 8 Jun 2015 11:59:31 -0300 > > Anderson Valadares wrote: > > > > > Hi > >

Re: [GENERAL] Momentary Delay

On Mon, 15 Jun 2015 17:48:54 -0300 Anderson Valadares wrote: > 2015-06-12 19:56 GMT-03:00 Bill Moran : > > > Please do not remove the mailing list from replies. See below. > > > > On Fri, 12 Jun 2015 09:21:19 -0300 > > Anderson Valadares wrote: > > > &g

Re: [GENERAL] Compression function

ase function that can be used to compress data; you'd have to write your own or do it at the application level. -- Bill Moran -- 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] serialization failure why?

de: Canceled on identification as a pivot, during > commit attempt. > HINT: The transaction might succeed if retried. > > and > > ERROR: could not serialize access due to read/write dependencies among > transactions > DETAIL: Reason code: Canceled on commit attempt with conflict in from > prepared pivot. > HINT: The transaction might succeed if retried. > > Thanks! -- Bill Moran -- 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] Momentary Delay

On Wed, 17 Jun 2015 10:33:37 -0300 Anderson Valadares wrote: > 2015-06-15 18:19 GMT-03:00 Bill Moran : > > > On Mon, 15 Jun 2015 17:48:54 -0300 > > Anderson Valadares wrote: > > > > > 2015-06-12 19:56 GMT-03:00 Bill Moran : > > > > > > > P

Re: [GENERAL] Select query regarding info

uld emit a warning when you used the non-standard way > of escaping single quotes (unless you explicitly turned that off) > > > Could you please provide below information. > How to change standard_conforming_strings value of postgresql.conf? I have > checked but this option is

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

be a BEFORE trigger FOR EACH ROW. Otherwise, the returned value won't do anything. It should read like this: CREATE TRIGGER trigger_name BEFORE INSERT ON table_ebscb_spa_log02 FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable(); If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger won't work as desired. The other thing about assignment being := was already mentioned. -- Bill Moran -- 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] very slow queries and ineffective vacuum

2768 Batches: 2 Memory Usage: 159kB > -> Seq Scan on table84 table84 (cost=0.00..14600.96 > rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1) > Total runtime: 13458.301 ms > (12 rows) > > Thank you again for your advice and I hope that with your help I'll be able > to solve this issue. > > Best regards. > Lukasz -- Bill Moran -- 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] Fwd: PostgreSQL & VMWare

dd capacity at minimal cost. There's nothing like a VM where you never know what the performance will be because you never know when some other VMs (completely unrelated to you and/or your work) will saturate the IO with some ridiculous grep recursive command or something. -- Bill Moran

Re: [GENERAL] Backup Method

ng that if your database has a lot of indexes, the pg_dump might actually be faster. But the only way to know is to try it out on your particular system. -- Bill Moran -- 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] Postgresql jsonb

far as when things get evicted from memory, you'll have to look at the source code, but it's your typical "keep the most commonly needed data in memory" algorithms. What problem are you seeing? What is your performance requirement, and what is the observed performance? I as

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

. > However, I know from experience that's not entirely true, (although it's not > always easy to measure all aspects of your I/O bandwith). > > Am I missing something? -- Bill Moran -- 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] Anyone interested in a Pittsburgh-area Postgres users'

gt; and we'll see where it goes ... > > > > regards, tom lane > > > -- Bill Moran -- 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 somebody explain what is the meaning for HashAggregate?

sort by oid then in the > oid group sort by relname? > Can somebody explain what does the database done for hashAggregate? It combines the values for oid and relname for each returned row, generates a hashkey for them, then uses that hashkey to aggregate (compute the GROUP BY, essentially, in th

Re: [GENERAL] Best way to sync table DML between databases

he best way to synchronise JUST the changes on a table > between servers please? Sounds like a problem custom-made to be solved by Slony: http://slony.info/ -- Bill Moran -- 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 postgresql for session

the session data in a Postgres table with great success. -- Bill Moran -- 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] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

ackers list so the developers can chime in. My opinion is that this is a bug, but it's an obscure enough bug that it's not surprising that it's gone unfixed for a while. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] I'm starting a PostgreSQL user's group in Pittsburgh

things like user's groups, and it does a pretty good job of letting us coordinate activities. Basic membership on the site is free and includes participating in as many groups as you desire. (it only costs something if you want to host your own group). Hope to see you soon. -- Bill Moran

Re: [GENERAL] Service not starting on Ubuntu 15.04

of the server, or have some command that tests to ensure the server is started and blocks until it is before running the create command. The only point I'm unclear on is whether you've confirmed that Postgres actually _is_ started once the server is up (albiet without the CREATE statem

Re: [GENERAL] ??: postgres cpu 100% need help

the problem occurs, and the contents of the pg_locks table when the problem is occurring. -- Bill Moran -- 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] Auto-analyse on insert operations

les every 5 minutes (based on the config you show) ... so are you doing the inserts then checking the table without leaving enough time in between for the system to wake up and notice the change? -- Bill Moran -- 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] Auto-analyse on insert operations

On Wed, 4 Nov 2015 16:43:57 +0100 Bertrand Roos wrote: > > Le 04/11/2015 14:55, Bill Moran a écrit : > > On Wed, 4 Nov 2015 14:32:37 +0100 > > Bertrand Roos wrote: > >> I try to configure auto-analyse task with postgresql 9.4. > >> I have the following

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

gt; return false if count is less it returns true. An exclusion constraint might be a better solution. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] The consequenses of interrupted vacuum

gets interrupted. I'm trying to understand if the partial runs are at least making _some_ progress so the next vacuum has less to do, or if this is a serious problem that I need to fiddle with tuning to fix. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To ma

Re: [GENERAL] The consequenses of interrupted vacuum

just noticed it on a particularly problematic day last time I looked. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Unexplained statistics reset? Help tracking it down.

t be a bug in Postgres, does anyone have any suggestions on what other ways the stats could be reset that I need to check on? Has anyone else experienced this to lend credence to the possibility that it's a bug? I have no clue how to reproduce it, as the occurrance is rare and still seems rando

[GENERAL] pg_repack and Postgres versions > 9.4

any more. Anyone know? Or, alternatively, anyone have another option to get the same job done? -- Bill Moran -- 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] controlling memory management with regard to a specific query (or groups of connections)

rest of the data on the other database server configured with higher shared_buffers. I know these probably aren't the kind of answers you're looking for, but I don't have anything better to suggest; and the rest of the mailing list seems to be devoid of ideas as well. -- Bill Mo

Re: [GENERAL] JSONB performance enhancement for 9.6

E EXTERNAL; ? The default storage for a JSONB field is EXTENDED. Switching it to EXTERNAL will disable compression. You'll have to insert your data over again, since this change doesn't alter any existing data, but see if that change improves performance. -- Bill Moran -- Sent via pgsq

Re: [GENERAL] JSONB performance enhancement for 9.6

knowledge) they're the best answers available at this time. I'd really like to build the alternate TOAST storage, but I'm not in a position to start on a project that ambitious right ... I'm not even really keeping up with the project I'm currently supposed to be doing. -- Bill Moran -- 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] AccessExclusiveLock on tuple?

If it's happening frequently, you'll want to investigate what process is holding the locks for so long and see what can be done about it. -- Bill Moran -- 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] AccessExclusiveLock on tuple?

On Wed, 2 Dec 2015 09:31:44 -0800 Christophe Pettus wrote: > > On Dec 2, 2015, at 9:25 AM, Bill Moran wrote: > > > No. See the section on row level locks here: > > http://www.postgresql.org/docs/9.4/static/explicit-locking.html > > That wasn't quite my ques

Re: [GENERAL] JDBC and inet type

The third solution is probably _really_ the correct one, from a pedantic standpoint, but it's a bit more work to implement. -- Bill Moran -- 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] Memory Leak executing small queries without closing the connection - FreeBSD

postgres [local] SELECT > > -- idle again, memory still allocated > > 26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 1:22.54 > > postgres: postgres postgres [local] idle > > > > Memory will only be released if psql is exited. According to the > > PostgreSQL design memory should be freed when the transaction completed. > > > > top commands on FreeBSD: top -SaPz -o res -s 1 > > top commands on Linux: top -o RES d1 > > > > Config: VMs with 4GB of RAM, 2 vCPUs > > shared_buffers = 2048MB # min 128kB > > effective_cache_size = 2GB > > work_mem = 892MB > > wal_buffers = 8MB > > checkpoint_segments = 16 -- Bill Moran -- 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] Memory Leak executing small queries without closing the connection - FreeBSD

On Sun, 13 Dec 2015 16:35:08 +0100 Gerhard Wiesinger wrote: > Hello Bill, > > Thank you for your response, comments inline: > > On 13.12.2015 16:05, Bill Moran wrote: > > On Sun, 13 Dec 2015 09:57:21 +0100 > > Gerhard Wiesinger wrote: > >> some further de

Re: [GENERAL] Memory Leak executing small queries without closing the connection - FreeBSD

Inuse > >> OK, but why do we then get: kernel: swap_pager_getswapspace(4): failed? > > Just judging from the name of the function, I would bet this is a direct > > result of having only 512M of swap configured. As Bill already pointed > > out, that's a pretty usele

Re: [GENERAL] Memory Leak executing small queries without closing the connection - FreeBSD

On Sun, 13 Dec 2015 22:23:19 +0100 Gerhard Wiesinger wrote: > On 13.12.2015 21:14, Bill Moran wrote: > > Wait ... this is a combined HTTP/Postgres server? You didn't mention that > > earlier, and it's kind of important. > > > > What evidence do you have

Re: [GENERAL] Test disk reliability (or HGST HTS721010A9E630 surprisingly reliable)

sync, which is the only thing diskchecker.pl tests for. > > > > I was thinking that since the disk have a 32M write-cache (with not > battery) it would lie to the OS (and postgres) about when data are really > on disk (not in the disk write cache). But maybe that thinking was wrong

Re: [GENERAL] uuid-ossp: Performance considerations for different UUID approaches?

seriously doubt that trying to make your UUIDs generate in a predictable fashon will produce any measurable improvement, and I see no evidence in the articles you cited that claims otherwise have any real basis or were made by anyone knowledgeable enough to know. -- Bill Moran -- Sent via p

Re: [GENERAL] Enforcing referential integrity against a HSTORE column

go, you may want to try something more along the lines of this for your check: SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN (akeys($1))); Not tested, so it's possible that I have some typo or something; but overall I've found that the NOT EXISTS construct

Re: [GENERAL] Code of Conduct: Is it time?

t will always involve _some_ pain, but less is better. I've done the job of #3 with other groups, and 99% of the time there was nothing to do. The one incident I had to handle was terrible, but at least I had some guidance on how to deal with it. -- Bill Moran -- Sent vi

  1   2   3   4   5   6   7   8   9   10   >