Re: [GENERAL] PGSQL: listing db/role and user/role relationships

On Mon, Nov 18, 2013 at 4:00 AM, Felipe Gasper wrote: > One more question: how “stable” are these interfaces? Are they by chance > available via information_schema? Enough stable that they have not changed so much since 8.3. But I'm not used to the information_schema, so I don't know which are e

[GENERAL] Suggestion: pg_dump self-cleanup if out-of-disk

Having recently had a pg_dump error out due to not having enough disk it occurs to me that it would be nice for pg_dump to remove the partial dump file it was creating (if possible/known) instead of having it sit around taking up that last bit of available space and itself being unusable for restor

[GENERAL] Primary Key Index Bloat?

Hi, I have a table with the following usage characteristic: INSERT bulk data using INSERT INTO ... SELECT ... FROM -- this table uses a varchar(50) for the PK -- the PK is rarely (effectively never unless a mistake was made) altered -- always appending to the existing table; some bulk deletions i

[GENERAL] Help : Sum 2 tables based on key from other table

Dear Friends, Please help for the select command, as i had tried many times and always can not display the result as what i want. I am looking for the solution on google but still can not found the right answer to solve the problem. I have 3 tables : Table A ProductID Produc

Re: [GENERAL] Sum 2 tables based on key from other table

Hengky Lie wrote > Dear David, > Thanks for your reply. > > Table A is product table contains ProductID, Name, Supplierid etc. > Table B is initial stock contains ProductID, Qty > Table C is the transaction table contains ProductID, date, in, out, > remarks, etc > > As i use trigger to write to a

Re: [GENERAL] Sum 2 tables based on key from other table

Dear David, Thanks for your reply. Table A is product table contains ProductID, Name, Supplierid etc. Table B is initial stock contains ProductID, Qty Table C is the transaction table contains ProductID, date, in, out, remarks, etc As i use trigger to write to a log file for any INSERT, UPDATE an

Re: [GENERAL] What does this error message mean?

Agreed. Although given that you can cast text to unknown, and NULL to text, it's not intuitively clear why this would have to fail absent replanning. However, knowing nothing about Postgres internals, I'm happy to take your word for it! Thanks again. Ken On Sun, Nov 17, 2013 at 7:59 PM, Tom La

Re: [GENERAL] Composite types or composite keys?

On 18 Nov 2013, at 2:24 pm, Chris Travers wrote: > > I haven't done work with this so I am not 100% sure but it seems to me based > on other uses I have for table inheritance that it might work well for > enforcing interfaces for natural joins. The one caveat I can imagine is that > there ar

Re: [GENERAL] What does this error message mean?

Ken Tanzer writes: > But thinking about it some more, the function runs one of 5 possible > queries. 4 of them select NULL as comment (no cast), while the fifth (and > the one that caused this error) selects 'a string'. Ah. Fixing that so all the variants produce the same (explicit) type should

Re: [GENERAL] Sum 2 tables based on key from other table

Hengky Lie wrote > 1. I want to select all productID from Table A where supplierID='XXX'. > > 2. Based on list from Step.1 : sum the initialstock from Table B > > 3. Based on list from Step 1 : Sum (in-out) from Table C where date > <'BEGINNING DATE' > > 4. Based on list from Step 1 : Sum (in)

Re: [GENERAL] What does this error message mean?

> > The type of that comment field hasn't changed Oh, and I'm going to slight eat my words, or at least elaborate. That comment field has been in all the views unchanged. Until Tuesday, though, the field wasn't being used or referenced in the function. So that line 195 is actually new as of Tu

Re: [GENERAL] What does this error message mean?

Ken Tanzer writes: > And if this error was from the Friday schema changes, would it have > auto-corrected itself so it only happened the one time? Starting a fresh session would've "auto-corrected" it ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-

Re: [GENERAL] Composite types or composite keys?

On 16 Nov 2013, at 3:01 am, Merlin Moncure wrote: > > Well, here are the downsides. Composite types: > *) are more than the sum of their parts performance-wise. So there is > a storage penalty in both the heap and the index > *) can't leverage indexes that are querying only part of the key > *

[GENERAL] Sum 2 tables based on key from other table

Dear Friends, Please help for the select command, as i had tried many times and always can not display the result as what i want. I am looking for the solution on google but still can not found the right answer to solve the problem. I have 3 tables : Table A ProductID Produc

Re: [GENERAL] Composite types or composite keys?

On Sun, Nov 17, 2013 at 6:57 PM, Tony Theodore wrote: > > On 15 Nov 2013, at 8:04 pm, Chris Travers wrote: > > > > In general, if you don't know you need composite types, you don't want > them. You have basically three options and the way you are doing it is the > most typical solution to the pr

Re: [GENERAL] What does this error message mean?

On Sun, Nov 17, 2013 at 6:20 PM, Tom Lane wrote: > Ken Tanzer writes: > > Hi. I got an error message reported to me that I've never seen before, > and > > I'm not quite sure what it means or what would cause it. > > > ERROR: type of parameter 70 (text) does not match that when preparing > the

Re: [GENERAL] PGSQL: listing db/role and user/role relationships

On 17.11.13 2:56 AM, Luca Ferrari wrote: On Sat, Nov 16, 2013 at 1:19 AM, Felipe Gasper wrote: Hi all, How can I retrieve: 1) each role’s privileges on a given DB Do you mean pg_database.datacl? http://www.postgresql.org/docs/current/static/catalog-pg-database.html 2) which use

Re: [GENERAL] Composite types or composite keys?

On 15 Nov 2013, at 8:04 pm, Chris Travers wrote: > > In general, if you don't know you need composite types, you don't want them. > You have basically three options and the way you are doing it is the most > typical solution to the problem The current way is much easier since I discovered th

Re: [GENERAL] What does this error message mean?

Ken Tanzer writes: > Hi. I got an error message reported to me that I've never seen before, and > I'm not quite sure what it means or what would cause it. > ERROR: type of parameter 70 (text) does not match that when preparing the > plan (unknown) CONTEXT: PL/pgSQL function > generate_payments(

Re: [GENERAL] Postgres as In-Memory Database?

On 11/17/2013 4:46 PM, Edson Richter wrote: There is no reason to wait for fsync in slow disks to guarantee consistency... If database server crashes, then it just need to "redo" log transactions from fast disk into slower data storage and database server is ready to go (I think this is Sybas

Re: [GENERAL] Postgres as In-Memory Database?

Em 17/11/2013 22:02, Gavin Flower escreveu: On 18/11/13 12:53, Stefan Keller wrote: Hi Martijn 2013/11/17 Martijn van Oosterhout > wrote: > If your dataset fits in memory then the problem is trivial: any decent > programming language provides you with all the necessar

[GENERAL] What does this error message mean?

Hi. I got an error message reported to me that I've never seen before, and I'm not quite sure what it means or what would cause it. When I re-run the query now, it runs without complaint, so the problem seems to have gone away. Which of course I don't understand either! Would be nice to know fo

Re: [GENERAL] Postgres as In-Memory Database?

On 18/11/13 12:53, Stefan Keller wrote: Hi Martijn 2013/11/17 Martijn van Oosterhout > wrote: > If your dataset fits in memory then the problem is trivial: any decent > programming language provides you with all the necessary tools to deal > with data purely in memory

Re: [GENERAL] Postgres as In-Memory Database?

2013/11/18 Andreas Brandl wrote: > What is your use-case? It's geospatial data from OpenStreetMap stored in a schema optimized for PostGIS extension (produced by osm2pgsql). BTW: Having said (to Martijn) that using Postgres is probably more efficient, than programming an in-memory database in a

Re: [GENERAL] Postgres as In-Memory Database?

Hi Martijn 2013/11/17 Martijn van Oosterhout wrote: > > If your dataset fits in memory then the problem is trivial: any decent > programming language provides you with all the necessary tools to deal > with data purely in memory. What about Atomicity, Concurrency and about SQL query language an

Re: [GENERAL] Postgres as In-Memory Database?

Hi Stefan, > How can Postgres be used and configured as an In-Memory Database? > we've put the data directory on our buildserver directly on a ramdisk (e.g. /dev/shm) to improve build times. Obviously you then don't care too much about durability here, so one can switch off all related settin

Re: [GENERAL] Postgres as In-Memory Database?

Em 17/11/2013 20:46, Andreas Brandl escreveu: Edson, Em 17/11/2013 19:26, Stefan Keller escreveu: Hi Edson As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsoleteand interesting to discuss (but that was'nt exactly what I asked above).

Re: [GENERAL] Postgres as In-Memory Database?

Edson, > Em 17/11/2013 19:26, Stefan Keller escreveu: > > Hi Edson > > > > As Rob wrote: Having a feature like an in-memory table like SQLite > > has > > [1] would make application cahces obsoleteand interesting to > > discuss > > (but that was'nt exactly what I asked above). > > Hi, Stephan, >

Re: [GENERAL] Postgres as In-Memory Database?

On Sun, Nov 17, 2013 at 10:33:30PM +0100, Stefan Keller wrote: > I think I have to add, that pure speed of a read-mostly database is the > main scenario I have in mind. > Duration, High-availability and Scaling out are perhaps additional or > separate scenarios. > > So, to come back to my question

Re: [GENERAL] Postgres as In-Memory Database?

Em 17/11/2013 19:26, Stefan Keller escreveu: Hi Edson As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsoleteand interesting to discuss (but that was'nt exactly what I asked above). Hi, Stephan, I don't think any feature you add to d

Re: [GENERAL] Postgres as In-Memory Database?

Hi Edson, On 2013/11/17 Edson Richter you wrote: > One question: would you please expand your answer and explain how would this adversely affect async replication? Is this a question or a hint (or both) :-)? Of course almost all non-durable settings [1] will delay replication. I think I have to

Re: [GENERAL] Postgres as In-Memory Database?

Hi Edson As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsolete and interesting to discuss (but that was'nt exactly what I asked above). --Stefan [1] http://www.sqlite.org/inmemorydb.html [2] http://www.postgresql.org/docs/9.1/static/n

Re: [GENERAL] Partitioning and triggers

Em 17/11/2013 18:45, Jeff Janes escreveu: On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter mailto:edsonrich...@hotmail.com>> wrote: Dear community, In documentation, when partitioning tables, it is said that "Optionally, define a trigger or rule to redirect data inserted into the

Re: [GENERAL] Partitioning and triggers

On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter wrote: > Dear community, > > In documentation, when partitioning tables, it is said that "Optionally, > define a trigger or rule to redirect data inserted into the master table to > the appropriate partition." > Is the trigger creation optional? I mea

Re: [GENERAL] simple query with radically different plan after 9.0 -> 9.2 upgrade

On Tue, Nov 12, 2013 at 2:47 PM, Tom Lane wrote: > > That's right, we store 90 days and roll up data older than that into a > > different table. > > Ah-hah. The default statistics target is 100, so indeed ANALYZE is going > to be able to fit every date entry in the table into the > most-common-v

[GENERAL] Partitioning and triggers

Dear community, In documentation, when partitioning tables, it is said that "Optionally, define a trigger or rule to redirect data inserted into the master table to the appropriate partition." Is the trigger creation optional? I mean, partitioning will not work as expected if we don't have the

Re: [GENERAL] Postgres as In-Memory Database?

Em 17/11/2013 12:15, rob stone escreveu: On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote: How can Postgres be used and configured as an In-Memory Database? Does anybody know of thoughts or presentations about this "NoSQL feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at

Re: [GENERAL] Postgres as In-Memory Database?

On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote: > How can Postgres be used and configured as an In-Memory Database? > > > Does anybody know of thoughts or presentations about this "NoSQL > feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon > 2010)? > > > Given, say

Re: [GENERAL] Postgres as In-Memory Database?

Em 17/11/2013 10:00, Michael Paquier escreveu: On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller wrote: How can Postgres be used and configured as an In-Memory Database? Does anybody know of thoughts or presentations about this "NoSQL feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy

Re: [GENERAL] Postgres as In-Memory Database?

On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller wrote: > How can Postgres be used and configured as an In-Memory Database? > > Does anybody know of thoughts or presentations about this "NoSQL feature" - > beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)? > > Given, say 128 GB memor

[GENERAL] Postgres as In-Memory Database?

How can Postgres be used and configured as an In-Memory Database? Does anybody know of thoughts or presentations about this "NoSQL feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)? Given, say 128 GB memory or more, and (read-mostly) data that fit's into this, what are

[GENERAL] N prefix and ::bpchar

Hello, I am not good at English. I have a question. There are the following data. create table chartbl ( caseno int, varchar5 varchar(5) ); insert into chartbl values(1, ' '); insert into chartbl values(2, ''); The same result with the following two queries is obtained. select * from chartbl

Re: [GENERAL] PGSQL: listing db/role and user/role relationships

On Sat, Nov 16, 2013 at 1:19 AM, Felipe Gasper wrote: > Hi all, > > How can I retrieve: > > > 1) each role’s privileges on a given DB > Do you mean pg_database.datacl? http://www.postgresql.org/docs/current/static/catalog-pg-database.html > 2) which users have access to a given role > 3)