Re: [GENERAL] Instangram is powered by PostgreSQL
unclesam wrote: just wanna to share that Instagram use PostgreSQL. excellent ! this company worth for 1 billion is powered by PostgreSQL Click link below http://highscalability.com/blog/2012/4/9/the-instagram-architecture-facebook-bought-for-a-cool-billio.html http://instagram-engineering.tumblr.com/post/20541814340/keeping-instagram-up-with-over-a-million-new-users-in Hopefully that will infect Facebook, which is MySQL otherwise, or at least hopefully MySQL won't infect Instagram. -- Darren Duncan -- 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 pgstat wait timeout question
I am running windows 2003 R2 (64 bit) - PostgreSQL 9.0.6 - 64bit. Today PostgreSQL has alot pgstat wait timeout in log. How to fix the PostgreSQL WARNING?? Tuan Hoang ANh
[GENERAL] Download not found for SEPostgreSQL
Hi, We would like to implement the security-enhanced postgresql (SEPostgreSQL ) on SUSE SP1. However we are unable to find the rpm packages. Is SEPostgreSQL still available? We are using postgreSQL 9.1 and would like to use 9.1 version of SEPostgreSQL but the rpm download is not available. Please provide me any links for download of SEPostgreSQL for SUSE SP1. Perhaps link for Red Hat too for evaluation. Regards, Eye Gee
Re: [GENERAL] Download not found for SEPostgreSQL
On 04/09/12 11:39 PM, Eye Gee wrote: We would like to implement the security-enhanced postgresql (SEPostgreSQL ) on SUSE SP1. However we are unable to find the rpm packages. Is SEPostgreSQL still available? We are using postgreSQL 9.1 and would like to use 9.1 version of SEPostgreSQL but the rpm download is not available. Please provide me any links for download of SEPostgreSQL for SUSE SP1. Perhaps link for Red Hat too for evaluation. http://code.google.com/p/sepgsql/ dunno if anyone is building RPMs, I kind of got the idea it was mostly experimental. looks like its for PG 8.2 and 8.3 ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Download not found for SEPostgreSQL
On 2012-04-10 09:40, John R Pierce wrote: On 04/09/12 11:39 PM, Eye Gee wrote: We would like to implement the security-enhanced postgresql (SEPostgreSQL ) on SUSE SP1. However we are unable to find the rpm packages. Is SEPostgreSQL still available? We are using postgreSQL 9.1 and would like to use 9.1 version of SEPostgreSQL but the rpm download is not available. Please provide me any links for download of SEPostgreSQL for SUSE SP1. Perhaps link for Red Hat too for evaluation. http://code.google.com/p/sepgsql/ dunno if anyone is building RPMs, I kind of got the idea it was mostly experimental. looks like its for PG 8.2 and 8.3 ? sepgsql is merged in PG release 9.1, without row-level security. For the current status in the upcoming development release 9.1, see http://www.postgresql.org/docs/devel/static/sepgsql.html regards, Yeb -- 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 pgstat wait timeout question
On Tue, Apr 10, 2012 at 12:39 PM, tuanhoanganh hatua...@gmail.com wrote: I am running windows 2003 R2 (64 bit) - PostgreSQL 9.0.6 - 64bit. Today PostgreSQL has alot pgstat wait timeout in log. How to fix the PostgreSQL WARNING?? Tuan Hoang ANh This warning message shows in database server log file, if a process gets interrupted at unfortunate moment, and doesn't get to run for a very long time. --Raghu
[GENERAL] 答复: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?
I see if no standby connect to master when synchronous_standby_names = '*', all commit will delay to standby connect to master. It is good. So I think the commit is sync between master and standby, But why the master delete the WAL segment before the standby commit when the standby connected ? -邮件原件- 发件人: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] 代表 Condor 发送时间: 2012年4月9日 21:33 收件人: pgsql-general@postgresql.org 主题: Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ? On 09.04.2012 13:33, 乔志强 wrote: I use postgresql-9.1.3-1-windows-x64.exe on windows 2008 R2 x64. 1 master and 1 standby. The standby is a synchronous standby use streaming replication (synchronous_standby_names = '*', archive_mode = off), the master output: standby walreceiver is now the synchronous standby with priority 1 the standby output: LOG: streaming replication successfully connected to primary Then run the test program to write and commit large blob(10 to 1000 MB bytes rand size) to master server use 40 threads(40 sessions) in loop, The Master and standby is run on the same machine, and the client run on another machine with 100 mbps network. But after some minutes the master output: requested WAL segment XXX has already been removed the standby output: FATAL: could not receive data from WAL stream: FATAL: requested WAL segment XXX has already been removed Question: Why the master deletes the WAL segment before send to standby in synchronous mode? It is a streaming replication bug ? I see if no standby connect to master when synchronous_standby_names = '*', all commit will delay to standby connect to master. It is good. Use a bigger wal_keep_segments? But I think the master should keep all WAL segments not sent to online standby (sync or async). wal_keep_segments shoud be only for offline standby. If use synchronous_standby_names for sync standby, if no online standby, all commit will delay to standby connect to master, So wal_keep_segments is only for offline async standby actually. master server output: LOG: database system was interrupted; last known up at 2012-03-30 15:37:03 HKT LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/136077B0 LOG: record with zero length at 0/17DF1E10 LOG: redo done at 0/17DF1D98 LOG: last completed transaction was at log time 2012-03-30 15:37:03.148+08 FATAL: the database system is starting up LOG: database system is ready to accept connections LOG: autovacuum launcher started / the standby is a synchronous standby LOG: standby walreceiver is now the synchronous standby with priority 1 / LOG: checkpoints are occurring too frequently (16 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (23 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (24 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (20 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (22 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. FATAL: requested WAL segment 00010032 has already been removed FATAL: requested WAL segment 00010032 has already been removed FATAL: requested WAL segment 00010032 has already been removed LOG: checkpoints are occurring too frequently (8 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. FATAL: requested WAL segment 00010032 has already been removed standby server output: LOG: database system was interrupted while in recovery at log time 2012-03-30 1 4:44:31 HKT HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode LOG: redo starts at 0/16E4760 LOG: consistent recovery state reached at 0/12D984D8 LOG: database system is ready to accept read only connections LOG: record with zero length at 0/17DF1E68 LOG: invalid magic number in log file 0, segment 50, offset 6946816 LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 00 010032 has already been removed Well, that is not a bug, just activate archive_mode = on on the master server and set also wal_keep_segments = 1000 for
[GENERAL] Is this doable using Postgresql crosstab or some other function?
Hi folkes, this is my first message on this list. I have quite a challenging problem and my own skills seem not to be adequate for resolving it. I have a relational model where the basic idea is to store data vertically instead using traditional horizontal approach. So instead of having a row in a table with n columns, I have n rows with value columns for different data types. This makes the model very dynamic but also difficult to use. I try to describe the (simplified) core of the model (or at least the parts which have some meaning) in the following pseudo definition: Table values ID serial pk instanceID integer fk1 (never mind this) parametertypeID integer fk2 value_integer integer value_varchar character varying value_date date stamp timestamp Table parameters parametertypeID serial pk typeid integer fk1 parameternameID integer fk2 Table parameternames paramaternameID serial pk parametername character varying parameterdatatype integer or like enum(1,2,3) (this defines whether to use value_integer, value_varchar or value_date) From these three tables I would like to create a select statement where the response is the following (where parameters.typeid = x and values.timestamp = dd.mm. hh.mm.ss.xx) instanceID integer parameternames.parametername#1, value and datatype from value_integer, value_varchar or value_date parameternames.parametername#2, value and datatype from value_integer, value_varchar or value_date ... parameternames.parametername#n, value and datatype from value_integer, value_varchar or value_date Quite challanging, right? - mika - -- 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 this doable using Postgresql crosstab or some other function?
On 04/10/12 1:52 AM, m...@digikartta.net wrote: Quite challanging, right? yes. and quite contrary to the relational data model. whats the point of going to all this abstraction? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 this doable using Postgresql crosstab or some other function?
On Apr 10, 2012, at 1:52, m...@digikartta.net wrote: Quite challanging, right? Aside from the possibly extremely rare case where this is actually functional the only challenging task is to tell off whomever gave you the idea/requirement that your schema is desirable - even if that person is yourself. That aside, judicious use of CASE constructs, and probably UNION, will get you your answers. Keep in mind that you likely need to end up casting all of your values to varchar/text in the end. You may also want to look at the HSTORE extension. Given the generic nature of your inquiry actual specific advice is impossible to give. David J. -- 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 this doable using Postgresql crosstab or some other function?
Let's say I have tens or hundreds or thousands of feature (instance) types. Each of them would need its own table. Let's say I give for the application administrator, a possibility to create new feature types? He/she merely knows what's the database is. So I need a model which is capable for offering dynamic table structures. I already have implemented parts which allow creating these types, creating instances of them and populating those instances with data. Now I have to come up with a method for flattening that data into one view so that it can be used directly. - mika - On Tue, 10 Apr 2012 01:58:46 -0700, John R Pierce pie...@hogranch.com wrote: On 04/10/12 1:52 AM, m...@digikartta.net wrote: Quite challanging, right? yes. and quite contrary to the relational data model. whats the point of going to all this abstraction? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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 this doable using Postgresql crosstab or some other function?
This schema is in use in one quite big system, which utilizes different DB than Postgresql. It is fully functional. - mika - On Tue, 10 Apr 2012 02:19:14 -0700, David Johnston pol...@yahoo.com wrote: On Apr 10, 2012, at 1:52, m...@digikartta.net wrote: Quite challanging, right? Aside from the possibly extremely rare case where this is actually functional the only challenging task is to tell off whomever gave you the idea/requirement that your schema is desirable - even if that person is yourself. That aside, judicious use of CASE constructs, and probably UNION, will get you your answers. Keep in mind that you likely need to end up casting all of your values to varchar/text in the end. You may also want to look at the HSTORE extension. Given the generic nature of your inquiry actual specific advice is impossible to give. David J. -- 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 this doable using Postgresql crosstab or some other function?
On Tue, Apr 10, 2012 at 4:27 AM, m...@digikartta.net wrote: Let's say I have tens or hundreds or thousands of feature (instance) types. Each of them would need its own table. Let's say I give for the application administrator, a possibility to create new feature types? He/she merely knows what's the database is. So I need a model which is capable for offering dynamic table structures. I already have implemented parts which allow creating these types, creating instances of them and populating those instances with data. Now I have to come up with a method for flattening that data into one view so that it can be used directly. If you want a schemaless design in a relational database, you have a some options: EAV model, hstore, xml (soon json too) as data. I consider these to be niche solutions not well suited to general purpose data management. For the most part, SQL really only works properly with a well defined schema. Your incoming data looks to be EAV-ish. You can build horizontal structures with crosstab and what you're trying to do looks semi doable, but it's going to to be quite difficult. merlin -- 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] Instangram is powered by PostgreSQL
On Tue, Apr 10, 2012 at 12:50 AM, unclesam geekmat...@yahoo.com wrote: just wanna to share that Instagram use PostgreSQL. excellent ! this company worth for 1 billion is powered by PostgreSQL Click link below http://highscalability.com/blog/2012/4/9/the-instagram-architecture-facebook-bought-for-a-cool-billio.html http://instagram-engineering.tumblr.com/post/20541814340/keeping-instagram-up-with-over-a-million-new-users-in pretty neat to see postgres mentioned in high scalability environments. one of my favorite quotes from the article: Ubuntu Linux 11.04 (“Natty Narwhal”). Solid, other Ubuntu versions froze on them. merlin -- 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] measure time intervals
Thank you but my problem is precisely because I'm using Postrgresql on a windows computer and I cannot change that :-/ Vincent. On Fri, Apr 6, 2012 at 1:40 PM, John R Pierce pie...@hogranch.com wrote: On 04/05/12 7:00 AM, Vincent Dautremont wrote: These are not affected by system time changes, so time interval can be calculated even if the system time is changed by NTP or the user. That's why I can't use any function based on system time. properly configured Unix NTP doesn't step-change the clock, it very gently slows it down or speeds it up until it is accurate and maintains stability. and only root can change the clock on a Unix system, so this really shouldn't be a concern. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] measure time intervals
On Thu, Apr 5, 2012 at 9:00 AM, Vincent Dautremont vinc...@searidgetech.com wrote: Hi, I'm wondering ig it is possible to measure elapsed time between 2 particular queries in PostgreSQL. what I need is the equivalent of @@TIMETICKS in Transac-SQL or CLOCK_MONOTONIC in Unix or GetTickCount in Windows These are not affected by system time changes, so time interval can be calculated even if the system time is changed by NTP or the user. That's why I can't use any function based on system time. one way that will work is to write a C module for postgres that wraps the system call. that's a heavy dependency for such a small thing though. also FYI GetTickCount wraps around approximately every three and a half weeks. merlin -- 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] measure time intervals
On 10/04/2012 14:39, Vincent Dautremont wrote: Thank you but my problem is precisely because I'm using Postrgresql on a windows computer and I cannot change that :-/ Windows can adjust the time using NTP also, though I don't know how abruptly or otherwise it does it. Under your date time settings there should be an Internet time tab. There are also some Windows NTP clients which might be worth looking at. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] measure time intervals
Thank you. I was searching if there was such feature included in postgresql. I'll find another way around to achieve what I want with this. As I have to use windows NTP colient and not a 3rd party client I'll go check windows NTP client configuration, see if I can make it act as the Unix one does. Vincent. On Tue, Apr 10, 2012 at 9:48 AM, Raymond O'Donnell r...@iol.ie wrote: On 10/04/2012 14:39, Vincent Dautremont wrote: Thank you but my problem is precisely because I'm using Postrgresql on a windows computer and I cannot change that :-/ Windows can adjust the time using NTP also, though I don't know how abruptly or otherwise it does it. Under your date time settings there should be an Internet time tab. There are also some Windows NTP clients which might be worth looking at. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie
Re: [GENERAL] Is this doable using Postgresql crosstab or some other function?
Merlin, thanks for your reply. Semidoable? Let's break this into parts: 1) Is it possible to create a view which have dynamic number of colums based on the select response? 2) Is it possible to name view colums based on some data retrieved with select statement? My pseudo definition, which was already simplified, could be simlified more. Maybe I do that and try crosstab for starters. As I told John, this model is one that works fine in one large application using MS SQL-Server. This view I am trying to create, is something that the application mentioned doesn't include. The model itself has been proven to be all working and extremely dynamical. This is what I discussed with John outside the list, forgive me, I didn't notice that, neither did John I guess.. - mika - - John, dynamic structures. Even if they were only one and the administrator user changed one, it would have some challange on it. Actually I have built my app on top of Apache Cocoon, so using XML, as Cocoon utilizes SAX stream and everything is already in XML syntax, wouldn't be a bad option. But this question of mine is considering a part where the consumer is a GIS-Server which don't know anything about XML datastores. PostGIS extension on Postgresql makes it a suitable datastore. - mika - On Tue, 10 Apr 2012 02:37:39 -0700, John R Pierce pie...@hogranch.com wrote: On 04/10/12 2:24 AM, m...@digikartta.net wrote: Let's say I have tens or hundreds or thousands of feature (instance) types. Each of them would need its own table. Let's say I give for the application administrator, a possibility to create new feature types? He/she merely knows what's the database is. So I need a model which is capable for offering dynamic table structures. you have 1000s of different data structures (classes) in your application? really? it sounds to me like you're describing a key-value store. these have no ready method of implementing data integrity, and make a very poor fit to the relational model, resulting in very cumbersome queries that don't execute efficiently. you'll welcomed with open arms by the NoSQL community, however. store everything as XML and go to town with any number of cloudy storage systems. 10.4.2012 16:13, Merlin Moncure kirjoitti: On Tue, Apr 10, 2012 at 4:27 AM,m...@digikartta.net wrote: Let's say I have tens or hundreds or thousands of feature (instance) types. Each of them would need its own table. Let's say I give for the application administrator, a possibility to create new feature types? He/she merely knows what's the database is. So I need a model which is capable for offering dynamic table structures. I already have implemented parts which allow creating these types, creating instances of them and populating those instances with data. Now I have to come up with a method for flattening that data into one view so that it can be used directly. If you want a schemaless design in a relational database, you have a some options: EAV model, hstore, xml (soon json too) as data. I consider these to be niche solutions not well suited to general purpose data management. For the most part, SQL really only works properly with a well defined schema. Your incoming data looks to be EAV-ish. You can build horizontal structures with crosstab and what you're trying to do looks semi doable, but it's going to to be quite difficult. merlin -- 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 this doable using Postgresql crosstab or some other function?
On Tue, Apr 10, 2012 at 9:31 AM, Mika M Lehtonen m...@digikartta.net wrote: Merlin, thanks for your reply. Semidoable? Let's break this into parts: 1) Is it possible to create a view which have dynamic number of colums based on the select response? Mostly no. A hardwired restriction is that a view has a rigidly defined list of columns with defined types. You can skirt that restriction a couple of ways -- for example your view could be a single column text (or xml, or hstore) with the columns you want encoded into it. 2) Is it possible to name view colums based on some data retrieved with select statement? nope -- in fact the point of views is to create a well defined representation of something which is the opposite of what you are trying to do. My pseudo definition, which was already simplified, could be simlified more. Maybe I do that and try crosstab for starters. As I told John, this model is one that works fine in one large application using MS SQL-Server. This view I am trying to create, is something that the application mentioned doesn't include. The model itself has been proven to be all working and extremely dynamical. This is what I discussed with John outside the list, forgive me, I didn't notice that, neither did John I guess.. merlin -- 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 this doable using Postgresql crosstab or some other function?
On Tue, Apr 10, 2012 at 09:43:52AM -0500, Merlin Moncure wrote: Mostly no. A hardwired restriction is that a view has a rigidly defined list of columns with defined types. You can skirt that restriction a couple of ways -- for example your view could be a single column text (or xml, or hstore) with the columns you want encoded into it. I'm wondering whether a set-returning (or these days, I guess, table-returning) function or a polymorphic function might make sense here. I haven't read the use case carefully (and I probably won't), but it seems like it might not be impossible that way. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] [streaming replication] 9.1.3 streaming replication bug ?
On Mon, Apr 9, 2012 at 7:33 PM, 乔志强 qiaozhiqi...@leadcoretech.com wrote: Question: Why the master deletes the WAL segment before send to standby in synchronous mode? Otherwise the master might be filled up with lots of unsent WAL files and which might cause PANIC error in the master, when there is no standby. IOW, the master tries to avoid a PANIC error rather than termination of replication. It is a streaming replication bug ? No. It's intentional. If use synchronous_standby_names for sync standby, if no online standby, all commit will delay to standby connect to master, So wal_keep_segments is only for offline async standby actually. What if synchronous_commit is set to local or async? Regards, -- Fujii Masao -- 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] question about alternate ordering of results
Hi Tom, declaring another operator class helped. At first, however, results were sorted deifferent than expected. A little gdb session revealed that if fact only the FUNCTION 1 entry in the operator class is used Regards Wolfgang Hamann haman...@t-online.de writes: Now, in versions 8 and later the using - is rejected, the ordering op needs to be or member of a btree operator class. What is needed to create the old behaviour again - create a complete operator class, including new names for the unchanged equals/not equals function? Yes. It sounds like you have pretty much all the spare parts you need, you just have to collect them together into an opclass for each ordering you want. Is this relevant to performance? Somewhat, in that it helps the planner optimize ordering considerations. But IIRC the main argument for tightening it up was to catch mistakes wherein somebody says ORDER BY x USING , or some other operator that doesn't produce a consistent sort order. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgNext CFP still open
Hey, Just a small reminder that the CFP for PgNext in Denver is still open. Let's get those talks in! https://www.postgresqlconference.org/ JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] question about alternate ordering of results
haman...@t-online.de writes: declaring another operator class helped. At first, however, results were sorted deifferent than expected. A little gdb session revealed that if fact only the FUNCTION 1 entry in the operator class is used Well, yeah, the function had better match the operators. regards, tom lane -- 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] measure time intervals
On 04/10/12 6:48 AM, Raymond O'Donnell wrote: Windows can adjust the time using NTP also, though I don't know how abruptly or otherwise it does it. Under your date time settings there should be an Internet time tab. the built in Windows Internet Time does a time step every 24 hours(or something) to correct it, or at least it did this last time I looked into it. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pg 9.1.3 pg_crypto question
Hi,
Finally upgrading from 8.2.5 to 9.1.3 (got the latest release as of
3/12/2012)
OS: Ubuntu 11.10
The only issue I am encountering is in the pg_crypto/decrypt_iv/decode
No errors in the log, but here's what I am seeing on both 8.2.5 and 9.1.3,
I am hoping someone can help me out here:
8.2.5#
8.2.5=# select encode(encrypt_iv(text2bytea('Hello There'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc'), 'base64');
encode
--
rkMRWpnnbjaFoHyLmCD/bg==
(1 row)
8.2.5=# select decrypt_iv(decode('rkMRWpnnbjaFoHyLmCD/bg==', 'base64'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc');
decrypt_iv
-
Hello There
(1 row)
So, the 8.2.5 is working as it always has.
9.1.3 is just not decrypting nor throwing errors.
9.1.3#
9.1.3# select encode(encrypt_iv(text2bytea('Hello There'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc'), 'base64');
encode
--
rkMRWpnnbjaFoHyLmCD/bg==
(1 row)
9.1.3# select decrypt_iv(decode('rkMRWpnnbjaFoHyLmCD/bg==', 'base64'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc');
decrypt_iv
--
\x48656c6c6f205468657265
(1 row)
Thanking you in advance,
Aaron
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] efficient trigger function selection?
Suppose I have an RDF-style table (with columns for subject, predicate, various object types, and graph) and want to have dozens or even hundreds of trigger functions defined conditionally on the predicate, ie when predicate = 'your predicate here'. My guess is Postgres is quite efficient at determining which if any trigger functions to call, but I thought I'd ask. Thx, ken
[GENERAL] trigger when clause
Hi, Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? To make this a little more concrete, what is likely to perform better a) A single trigger with n if/else clauses b) A set of n triggers each using a different when clause. What if n is 10? What if n is 200? The when clause would just be comparing a text predicate column for equality. Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
Re: [GENERAL] Pg 9.1.3 pg_crypto question
Aaron Burnett aburn...@bzzagent.com writes:
9.1.3 is just not decrypting nor throwing errors.
9.1.3# select decrypt_iv(decode('rkMRWpnnbjaFoHyLmCD/bg==', 'base64'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc');
decrypt_iv
--
\x48656c6c6f205468657265
(1 row)
I think this is the same result, it's just being shown in hex.
See the bytea_output configuration parameter.
regards, tom lane
--
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] efficient trigger function selection?
On Tue, 2012-04-10 at 16:07 -0400, Kenneth Tilton wrote: Suppose I have an RDF-style table (with columns for subject, predicate, various object types, and graph) and want to have dozens or even hundreds of trigger functions defined conditionally on the predicate, ie when predicate = 'your predicate here'. My guess is Postgres is quite efficient at determining which if any trigger functions to call, but I thought I'd ask. I recommend measuring the overhead with some bogus no-op triggers; my guess is that it will be significant but maybe not too bad depending on what the rest of the application is doing. What are you trying to accomplish with so many triggers? Regards, Jeff Davis -- 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 9.1.3 pg_crypto question
Thanks, Tom. That was exactly it.
Best Regards
On 4/10/12 2:50 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Aaron Burnett aburn...@bzzagent.com writes:
9.1.3 is just not decrypting nor throwing errors.
9.1.3# select decrypt_iv(decode('rkMRWpnnbjaFoHyLmCD/bg==', 'base64'),
decode('bcRJvbqeWMPDXMtIP8pPOQ==', 'base64'), '',
'aes-cbc');
decrypt_iv
--
\x48656c6c6f205468657265
(1 row)
I think this is the same result, it's just being shown in hex.
See the bytea_output configuration parameter.
regards, tom lane
--
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] trigger when clause
On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote: Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? It's done with a linear scan of all triggers, testing the WHEN clause for each. To make this a little more concrete, what is likely to perform better a) A single trigger with n if/else clauses b) A set of n triggers each using a different when clause. Both are essentially linear. If you want to scale to a large number of conditions, I would recommend using one trigger in a fast procedural language, and searching for the matching conditions using something better than a linear search. To beat a linear search, you need something resembling an index, which is dependent on the types of conditions. For instance, if your conditions are: 00 = x 10 10 = x 20 20 = x 30 ... you can use a tree structure. But, obviously, postgres won't know enough about the conditions to know that a tree structure is appropriate from a given sequence of WHEN clauses. So, you should use one trigger and code the condition matching yourself. Regards, Jeff Davis -- 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] Resize numeric column without changing data?
On Mon, Apr 9, 2012 at 6:16 PM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-04-09 at 16:06 -0400, Lukas Eklund wrote: Ah. I must have not noticed that the typmod for views is not inherited automatically. I'm okay with developing a script to recreate the 15 or so views the depend on that table. What I'm trying to avoid is locking that table for a substantial amount of time. Thanks for the advice! FYI, later versions of postgres try to avoid rewrites of the table when possible for simple ALTERs like the one you're talking about. Are you using PostgreSQL 9.1? Try it out in a simple test case... maybe the lock is only held for an instant anyway. Some of these optimizations went in 9.2 (not released yet) but I think the one you need is in 9.1. I'm using 8.3 and while I was searching the google machine for information about resizing columns in this manner I came across the patches for varchar and numeric that have made their way into 9.1 and 9.2. I'm using those as additional ammunition to speed the upgrade from 8.3 to 9.1. I've made the change in a development environment and all the applications behave okay. I'm still testing stored procedures that use the table and working out a comprehensive testing plan before I make the change anywhere else. Thanks, Lukas -- 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] [streaming replication] 9.1.3 streaming replication bug ?
Thank you for this good feature and your reply. synchronous_commit is not set, default is on ? #synchronous_commit = on# synchronization level; on, off, or local Otherwise the master might be filled up with lots of unsent WAL files and which might cause PANIC error in the master, when there is no standby. IOW, the master tries to avoid a PANIC error rather than termination of replication. Can we have a config option for keep unsent WAL file for replication ? How can I do when I need a backup standby server and wal_keep_segments = 3 for save master disk usage(master will delete wal before send to standby now when heavy load, Need modify some config?) sync commit to master and standby (this is supportted now) My config file of master server: # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The = is optional.) Whitespace may be used. Comments are introduced with # # anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use pg_ctl reload. Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # postgres -c log_connections=on. Some parameters can be changed at run time # with the SET SQL command. # # Memory units: kB = kilobytesTime units: ms = milliseconds #MB = megabytes s = seconds #GB = gigabytes min = minutes # h = hours # d = days #-- # FILE LOCATIONS #-- # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '(none)' # write an extra PID file # (change requires restart) #-- # CONNECTIONS AND AUTHENTICATION #-- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) # Note: Increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directory = '' # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - Security and Authentication - #authentication_timeout = 1min # 1s-600s #ssl = off # (change requires restart) #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers # (change requires restart) #ssl_renegotiation_limit = 512MB# amount of data between renegotiations #password_encryption = on #db_user_namespace = off #
Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?
On Wed, Apr 11, 2012 at 10:06 AM, 乔志强 qiaozhiqi...@leadcoretech.com wrote: synchronous_commit is not set, default is on ? #synchronous_commit = on # synchronization level; on, off, or local Yes. Otherwise the master might be filled up with lots of unsent WAL files and which might cause PANIC error in the master, when there is no standby. IOW, the master tries to avoid a PANIC error rather than termination of replication. Can we have a config option for keep unsent WAL file for replication ? No. We discussed about such feature before, but it had failed to be committed. I think it's useful, so I hope it'll be usable in the future release. How can I do when I need a backup standby server and wal_keep_segments = 3 for save master disk usage(master will delete wal before send to standby now when heavy load, Need modify some config?) Yes, increase wal_keep_segments. Even if you set wal_keep_segments to 64, the amount of disk space for WAL files is only 1GB, so there is no need to worry so much, I think. No? #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each Increase checkpoint_segments. In this setting, I guess checkpoints run too frequently in heavy load, and WAL files are removed too aggressively. Regards, -- Fujii Masao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?
-- Forwarded message -- From: Michael Nolan htf...@gmail.com Date: Tue, Apr 10, 2012 at 9:47 PM Subject: Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ? To: Fujii Masao masao.fu...@gmail.com On Tue, Apr 10, 2012 at 9:09 PM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Apr 11, 2012 at 10:06 AM, 乔志强 How can I do when I need a backup standby server and wal_keep_segments = 3 for save master disk usage(master will delete wal before send to standby now when heavy load, Need modify some config?) Yes, increase wal_keep_segments. Even if you set wal_keep_segments to 64, the amount of disk space for WAL files is only 1GB, so there is no need to worry so much, I think. No? If you're writing records with a 100MB blob object in them, you definitely need to keep more than 3 WAL segments at a time, because at 16MB each that won't hold even one of your largest records. That's the kind of value added information that the DBA brings to the table that the database itself won't know, which is why one of the DBA's most important tasks is to properly configure the postgresql.conf file, and revise it as the database changes over time. -- Mike Nolan
[GENERAL] pltcl and modules
Hi, I recently found out about critcl, which does a fairly decent job of converting a C fragment inside a tcl procedure into a loadable module. Now, if I wanted to use the same code inside pltcl, would that be possible? If yes, any special precautions other than getting the file paths right? Regards Wolfgang Hamann -- 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] 9.1.3 Standby catchup mode
On Fri, Apr 6, 2012 at 1:35 AM, hans wulf lo...@gmx.net wrote: I am wondering how the catchup mode of a hot synchron slave server works on 9.1.3 if there is no WAL archive. Can the slave only request WALs that are still in the xlog directory of the master server? Or does the master regenerate some kind of fake log for the catchup mode? No. If the WAL file which the standby requests doesn't exist in the pg_xlog directory of the master, replication just fails. In this case, you need to take a fresh base backup and start the standby from that backup. E.g. in case of a slave failure I could use a weekly backup and let the catchup mode do the rest? Or does that only work if you use WAL archive? Or increase wal_keep_segments to high so that all WAL files which the standby requests are guaranteed to exist in the pg_xlog directory of the master. Regards, -- Fujii Masao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
