Re: [GENERAL] Extension table data
Just found something else rather disturbing. If you try to exclude the schema that the extension tables are in, their data is still output. Explicitly naming other schemas doesn't seem to dump the extension data. So the only way to avoid getting the extension data in a schema-only dump is to explicitly name all schemas but the one your extension is in, which I think is another bug you had actually fixed for 9.1.3 where extension data was always being dumped. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. 443.325.1357 x251 On Sat, Jun 9, 2012 at 2:56 AM, Keith Fiske wrote: > With the current design, I understand what you're saying now. Just > doing some more testing, I was able to do a pg_dump -Fc -s for the > entire database and looking through the resulting object list with > pg_restore -l I'm actually seeing the extension table data included in > the dump file. Doing a restore on the schema I put the extension in, I > see the COPY commands to restore the data. I think this is a serious > shortcoming, and a confusing state of affairs. The only way to get > this data out seems to be to do a full database dump and the > schema-only option to pg_dump is outputting data. > > Looking at the docs, I think the extension authors may have only had > configuration data in mind for extension tables. I don't see any > reason why we shouldn't be able to put any sort of table in our > extensions, some having actual data, not just config. That's actually > what I'm doing with my pg_jobmon extension, which could potentially > have millions of rows over time. Not having the tables included in the > extension definition would just make setting the whole thing up more > difficult and error prone. > > If extensions really are going to be self contained like this as far > as dumps, perhaps another option to pg_dump is needed, and have the > schema-only or data-only options be honored in that case as well. > > -- > Keith Fiske > Database Administrator > OmniTI Computer Consulting, Inc. > 443.325.1357 x251 > > > On Fri, Jun 8, 2012 at 11:38 PM, Tom Lane wrote: >> Keith Fiske writes: >>> I've read the documentation for extensions and how their data is not >>> normally dumped by pg_dump and how to configure the table so it should >>> dump its data >>> http://www.postgresql.org/docs/9.1/static/extend-extensions.html >>> However, after setting this option for the tables, the data is not >>> being dumped when I do a pg_dump of either individual tables or the >>> schema I've installed the extension to. >> >> IIRC, the design intention is that such a table's data would be dumped >> when (and only when) the extension is dumped. That is, I'd expect to >> see a "CREATE EXTENSION foo;" and then data for the extension's tables. >> The partial-dump scenarios you mention wouldn't dump extensions, hence >> not extension table data either. >> >> Whether this design is a good one is still under debate, but I think >> pg_dump is operating as designed here ... >> >> 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] Postgres 9.2 beta2 one-click installer on windows
(CCing Dave Page & Sachin Srivastava who work on the installer):
Short version is that the Pg installer appears to need to:
- Include the latest release of the redist because there's a security
fix in it
- use /passive not /q when invoking it
- treat return 5100 as success
- swear at Microsoft for not documenting this
Continued below.
On 06/08/2012 11:55 PM, Marc Watson wrote:
Executing C:\Users\Mark
Watson\AppData\Local\Temp\postgresql_installer\vcredist_x64.exe /q
Script exit code: 5100
Is there any chance you can get the version of the currently installed
MSVC++ redistributible(s) on your computer? They're listed in programs
and features in the control panel.
I suspect you have a newer version of the same general version (eg 2010
SP1) of the VC++ redist installed, and rather than silently ignore the
newer version it's failing.
This appears to be a fairly common issue. It's even encountered by the
Visual C++ IDE installer its self. The short-term workaround appears to
be for the Pg installer to ignore error code 5100. Longer term, I'd like
a way to tell the VC++ redist "don't be stupid, just silently take no
action if a newer version is present."
We should also probably be using "/passive /norestart" not "/q
/norestart" when invoking the installer, since that lets the redist
display useful error messages to the user but doesn't demand interaction
from them.
BTW, exit code 3100 ("succeeded but a reboot is required) should also be
handled if it isn't already.
People are using some scary workarounds, like:
http://notepad.patheticcockroach.com/1666/installing-visual-c-2010-and-windows-sdk-for-windows-7-offline-installer-and-installation-troubleshooting/
(Warning: comments are content-free zone full of me-too, plus people not
understanding what the redist does, confusing the installation of the
runtime for the us of VC++ with the "install redistributables" option
that copies them for _you_ to use in _your_ software, etc).
Also:
http://ta.speot.is/2012/04/09/visual-studio-2010-sp1-windows-sdk-7-1-install-order/
http://social.microsoft.com/Forums/en/crm/thread/db84d4c3-c58e-4ff9-997f-f4e0116bf043
http://blogs.msdn.com/b/astebner/archive/2010/10/20/10078468.aspx
BTW, IBM seems to install the redist as:
|vcredist_x86.exe /q /c:"msiexec /i vcredist.msi /qn /l*v
%temp%\vcredist_x86.log"|
... not sure why yet.
--
Craig Ringer
Re: [GENERAL] Extension table data
On 2012-06-09 08:56, Keith Fiske wrote: Looking at the docs, I think the extension authors may have only had configuration data in mind for extension tables. I don't see any reason why we shouldn't be able to put any sort of table in our extensions, some having actual data, not just config. True. We've made several extensions that consist only of table data, such as contents of medical codesystems, where the actual terminology comes from another extension that has functions and empty codesystem tables. I remember there were some issues making it. IIRC the table data would not be dropped on extension drop. regards, Yeb -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data -- 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] Extension table data
Keith Fiske writes: > Just found something else rather disturbing. If you try to exclude the > schema that the extension tables are in, their data is still output. This is a common misconception: extensions do not live within schemas. (An extension might own a schema, not the other way around.) So a dump with a -n switch is never going to select an extension. By and large, if the current behavior bothers you, ISTM it probably means you are using these tables in a way other than what the concept of an extension configuration table was meant for: namely, to hold configuration data that would be referenced by the functions in that extension, but would not normally be considered part of the user's data. There has been some talk of trying to cater for a more general notion of tables created by extensions, but we do not have a design or even a clear idea of a set of requirements for that. Perhaps it would be good if you explained what is your use-case --- why are you concerned about being able to manage these tables as if they were regular data? 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] pass NEW / OLD variable in trigger to table
Hello, I'm using some trigger functions on a view. Can I pass the NEW / OLD variable to the table of the view? An excerpt of my view shows: IF TG_OP = 'INSERT' then insert into mytable (name, data, commentdata, history) values (NEW.name, NEW.data, NEW.commentdata, NEW.history); ernd if; Can I do anything like "insert into mytable values (NEW)" ? Can I do this with update and delete also? I would like to pass the variable with the "action" to the table, so I need not to reference the fields Thanks Phil -- 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] Extension table data
On Sat, Jun 9, 2012 at 5:33 AM, Tom Lane wrote: > Keith Fiske writes: >> Just found something else rather disturbing. If you try to exclude the >> schema that the extension tables are in, their data is still output. > > This is a common misconception: extensions do not live within schemas. > (An extension might own a schema, not the other way around.) So a > dump with a -n switch is never going to select an extension. > > By and large, if the current behavior bothers you, ISTM it probably > means you are using these tables in a way other than what the concept of > an extension configuration table was meant for: namely, to hold > configuration data that would be referenced by the functions in that > extension, but would not normally be considered part of the user's data. > There has been some talk of trying to cater for a more general notion of > tables created by extensions, but we do not have a design or even a > clear idea of a set of requirements for that. Perhaps it would be good > if you explained what is your use-case --- why are you concerned about > being able to manage these tables as if they were regular data? > Here's a use case I have been thinking a lot about lately. I am not sure that extensions is the right vehicle for it, but it may be a good starting point. LedgerSMB is moving towards a more modular structure and LedgerSMB 1.5 will probably require PostgreSQL 9.1 or higher (owing to the writable CTE's). I have been looking at how the extension system can be used to simplify our maintenance and it helps quite a bit. However, one key aspect that would be really nice would be managing schema changes along with changing versions of an extension. For example, one might have a fixed asset module, and that module might have a series of stored procedures. We would have some tables that store configuration data regarding the module. For example we might have a table that stores info on stored procedures that track depreciation methods. The use of tables for configuration data in the current approach fits this nicely. However we might have other tables which store data, things like which fixed assets get depreciated in which ways, and the like. These may need to have columns added from time to time, or have other alter table operations performed. It would be nice to be able to manage these schema changes and upgrading the extension in the same framework. I am wondering if some sort of "database modules" framework might be helpful with modules possibly having extensions, but also having data tables. Best Wishes, Chris Travers -- 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] pass NEW / OLD variable in trigger to table
On 06/09/2012 05:49 AM, Philipp Kraus wrote: Hello, I'm using some trigger functions on a view. Can I pass the NEW / OLD variable to the table of the view? An excerpt of my view shows: IF TG_OP = 'INSERT' then insert into mytable (name, data, commentdata, history) values (NEW.name, NEW.data, NEW.commentdata, NEW.history); ernd if; Can I do anything like "insert into mytable values (NEW)" ? Can I do this with update and delete also? I would like to pass the variable with the "action" to the table, so I need not to reference the fields http://www.postgresql.org/docs/9.1/interactive/plpgsql-trigger.html See Example 39-4. A PL/pgSQL Trigger Procedure For Auditing Thanks Phil -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help to debug a code
hi, While using postgis with mapserver ... i am getting black image in place of raster image... please help ... LAYER NAME coolwktraster TYPE raster STATUS DEFAULT #CONNECTIONTYPE ogr DATA "PG:host=localhost port=5432 dbname='postgis' user='postgres' password='***' schema='public' table='test' " PROCESSING "NODATA=0" PROCESSING "SCALE=AUTO" PROJECTION "init=epsg:3003" END METADATA "wms_title" "OFC 1:10.000 single" "wms_srs" "EPSG:3003 EPSG:4326" END ... Error report: [Sat Jun 09 01:49:25 2012].727000 CGI Request 1 on process 2096 [Sat Jun 09 01:49:25 2012].729000 msDrawMap(): rendering using outputformat named png (AGG/PNG). [Sat Jun 09 01:49:25 2012].729000 msDrawMap(): WMS/WFS set-up and query, 0.000s [Sat Jun 09 01:49:25 2012].731000 msDrawRasterLayerLow(coolwktraster): entering. [Sat Jun 09 01:49:26 2012].252000 LoadGDALImage(coolwktraster): NODATA value 0 in GDAL file or PROCESSING directive largely ignored. Not yet fully supported for unclassified scaled data. The NODATA value is excluded from auto-scaling min/max computation, but will not be transparent. [Sat Jun 09 01:49:26 2012].259000 LoadGDALImage(coolwktraster): NODATA value 0 in GDAL file or PROCESSING directive largely ignored. Not yet fully supported for unclassified scaled data. The NODATA value is excluded from auto-scaling min/max computation, but will not be transparent. [Sat Jun 09 01:49:26 2012].265000 LoadGDALImage(coolwktraster): NODATA value 0 in GDAL file or PROCESSING directive largely ignored. Not yet fully supported for unclassified scaled data. The NODATA value is excluded from auto-scaling min/max computation, but will not be transparent. [Sat Jun 09 01:49:26 2012].269000 msDrawMap(): Layer 0 (coolwktraster), 0.540s [Sat Jun 09 01:49:26 2012].269000 msDrawMap(): Drawing Label Cache, 0.000s [Sat Jun 09 01:49:26 2012].269000 msDrawMap() total time: 0.542s [Sat Jun 09 01:49:26 2012].424000 msSaveImage(stdout) total time: 0.155s [Sat Jun 09 01:49:26 2012].425000 mapserv request processing time (loadmap not incl.): 0.698s [Sat Jun 09 01:49:26 2012].425000 msFreeMap(): freeing map at 025D1FE8.
[GENERAL] Extending SQL C-language multidimension arrays
I am using Postgrsql 9.0 and writing a C-language extension. The extension takes an n-dimension array and performs a lookup on that array. Right now I'm stuck on determining the dimensions of the array data passed in. No matter what I pass in as the array parameter (the first parameter) I'm getting a ARR_NDIM of 2. For example, I get an ARRY_DIM of 2 for each of these queries: SELECT getone(ARRAY[[10.1,11.2,12.3],[20.1,21.2,22.3]], POINT(1.0,1.0), 1.5, 1.0, 1.0); SELECT getone(ARRAY[[10.1,11.2,12.3],[20.1,21.2,22.3],[31.1,33.2,35.3],[44.1,45.2,46.3]], POINT(1.0,1.0), 1.5, 1.0, 1.0); To my way of thinking about these arrays, I should be getting the ARR_NDIM answer of 2 for the first query and an ARR_NDIM answer of 4 for the second one. Is this a bug or am I missing something? NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.
Re: [GENERAL] Extending SQL C-language multidimension arrays
Chris Schnaufer writes: > SELECT getone(ARRAY[[10.1,11.2,12.3],[20.1,21.2,22.3]], POINT(1.0,1.0), 1.5, > 1.0, 1.0); > SELECT > getone(ARRAY[[10.1,11.2,12.3],[20.1,21.2,22.3],[31.1,33.2,35.3],[44.1,45.2,46.3]], > POINT(1.0,1.0), 1.5, 1.0, 1.0); > To my way of thinking about these arrays, I should be getting the ARR_NDIM > answer of 2 for the first query and an ARR_NDIM answer of 4 for the second > one. No, those both look like 2-dimensional arrays to me. You'd need to look at the contents of the ARR_DIMS vectors to tell the difference between them. 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] is there a select for update insert if not exist type command?
I have a project where I will have two clients essentially doing the same things at the same time. The idea is that if one has already done the work, then the second one does not need to do it. I was hoping that adding a task related unique identifier to a table could be used to coordinate these client, something like a primary key and using select for update. The challenge I have is during the initial insert. One of the two clients will cause postgresql to log an error, which I would rather avoid (just seems dirty). Here is the time line; Both clients A and B becomes aware to do a task Client A or client B issues the "select for update ... if not exist do insert" type command The other client gets blocked on the "select for update. First client finishes insert/updates to record that it has delt with the task second client gets unblocked and reads the record realizing that the first client delt with the task already. It is the "select for update ... if not exist do insert" type command that I am ignorant of how to code. Anyone care to school me? Evan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a select for update insert if not exist type command?
On Sat, 9 Jun 2012 15:41:34 -0700 Evan Rempel wrote: > I have a project where I will have two clients essentially doing the > same things at the same time. The idea is that if one has already done the > work, then the second one does not need to do it. > > I was hoping that adding a task related unique identifier to a table > could be used to coordinate these client, something like a primary key and > using > select for update. > > The challenge I have is during the initial insert. One of the two clients > will cause postgresql > to log an error, which I would rather avoid (just seems dirty). > > Here is the time line; > > Both clients A and B becomes aware to do a task > > Client A or client B issues the "select for update ... if not exist do > insert" type command > The other client gets blocked on the "select for update. > > First client finishes insert/updates to record that it has delt with the task > > second client gets unblocked and reads the record realizing that the first > client delt with the task already. > > > It is the "select for update ... if not exist do insert" type command that I > am ignorant of how to code. > > Anyone care to school me? It's amazing to me how often I have this conversation ... How would you expect SELECT FOR UPDATE to work when you're checking to see if you can insert a row? If the row doesn't exist, there's nothing to lock against, and thus it doesn't help anything. FOR UPDATE is only useful if you're UPDATING a row. That being given, there are a number of ways to solve your problem. Which one you use depends on a number of factors. If it's x number of processes all contending for one piece of work, you could just exclusive lock the entire table, and do the check/insert with the table locked. This essentially creates a wait queue. If the processes need to coordinate around doing several pieces of work, you can put a row in for each piece of work with a boolean field indicating whether a process is currently working on it. Then you can SELECT FOR UPDATE a particular row representing work to be done, and if the boolean isn't already true, set it to true and start working. In my experience, you'll benefit from going a few steps forward and storing some information about what's being done on it (like the PID of the process working on it, and the time it started processing) -- it just makes problems easier to debug later. There are other approaches as well, but those are the two that come to mind. Not sure what your experience level is, but I'll point out that these kinds of things only work well if you're transaction management is correct. I have seen people struggle to get these kind of things working because they didn't really understand how transactions and locking interact, or they were using some sort of abstraction layer that does transaction stuff in such an opaque way that they couldn't figure out what was actually happening. Hope this helps. -- 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] is there a select for update insert if not exist type command?
You will find this reading a good start point: http://www.cs.uiuc.edu/class/fa07/cs411/lectures/cs411-f07-tranmgr-3.pdf There are no "fit all needs" cookbook about this, you will have to learn the theory about transactional database transaction management and locking mechanism and work on your solution. Wish you the best, Edson. Em 09/06/2012 19:41, Evan Rempel escreveu: I have a project where I will have two clients essentially doing the same things at the same time. The idea is that if one has already done the work, then the second one does not need to do it. I was hoping that adding a task related unique identifier to a table could be used to coordinate these client, something like a primary key and using select for update. The challenge I have is during the initial insert. One of the two clients will cause postgresql to log an error, which I would rather avoid (just seems dirty). Here is the time line; Both clients A and B becomes aware to do a task Client A or client B issues the "select for update ... if not exist do insert" type command The other client gets blocked on the "select for update. First client finishes insert/updates to record that it has delt with the task second client gets unblocked and reads the record realizing that the first client delt with the task already. It is the "select for update ... if not exist do insert" type command that I am ignorant of how to code. Anyone care to school me? Evan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a select for update insert if not exist type command?
> -Original Message- > > Both clients A and B becomes aware to do a task > Ideally you would have this aware-ness manifested as an INSERT into some kind of job table. The clients can issue the "SELECT FOR UPDATE" + "UPDATE" commands to indicate that they are going to be responsible for said task. You seem to combining "something needs to be done" with "I am able to do that something". You may not have a choice depending on your situation but it is something to think about - how can I just focus on implementing the "something needs to be done" part. If you want to avoid the errors appearing in the logs or client you could just wrap the INSERT command into a function and trap the duplicate key exception. It is hard to give suggestions when you are as vague as "becomes aware to do a task". Ideally even if you have multiple clients monitoring for "aware state" only one client should ever actually realize said awareness for a given task. In effect you want to serialize the monitoring routine at this level, insert the "something needs to be done" record, then serialize (for update) the "I am able to do that something" action. 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 there a select for update insert if not exist type command?
Thanks for the input. Dave also replied indicating that without more details it is difficult the really help. I was intentionally vague to see if there was some SQL standard way like to mysql "insert ... on duplicate update ... " syntax, or the proposed MSSQL merge command. Since not, I'll give a lot more detail without writing a book. We are working on a project where a syslog stream arrives and is parsed in real time. Every log message is considered to be an "event". Events can usually be ignored because they are "normal behaviour". Some events indicate a problem and should create an incident. Repetitions of the same event should not create a new incident if the current incident is not resolved. For redundancy, two independent systems will consume and process these events. Now for the part where postgresql comes in. When an event that should create an incident is encountered, only one incident should be created. The incident details are not known until such time as the event occurs, so no pre-population of tables can occur. So only one of the two server should perform the insert, and then update it with details of successive events, possibly ticketing system identification, date/time of last pager message that went out. Once the incident is placed into postgresql, everything is easy, "select for update", determine all that should take place like paging, updating tickets, recording date/time of last alert sent to administrators. It is just that first insert that is the challenge. One system does the insert, only to have the other do the "select for update". I would like to have an insert that is locked AND visible to other sessions. Exclusive lock on the table is an idea, but it serializes ALL new incident creation, and we only NEED to serialize for the same incident identifier. Since both (all) of the systems that will be processing the live log stream, in all likelihood all of the servers will always be working on the same data and thus the same incident, so will always be locking the same piece of data anyway, so the full table lock may not be any worse. I was thinking of using and advisory lock, which would also serialize everything, just like a table lock, but again, that may not be a problem since all processes work on the same data at the same time anyways. I could also use a custom "my_locks" table that just has rows with unique values that I do a "select for update" on to serialize everything. Again, no functional difference from table or advisory locks. A follow on questions; Is there anything inherently atomic about a stored procedure? Does the stored procedure simply run within the transaction context of where it is called from? begin transaction - select storedProc1 - select storedProc2 commit Would the actions of both stored procedures would be a single atomic action? Thanks again for lending me your experience, it can, and is, saving me days. Evan. From: Bill Moran [wmo...@potentialtech.com] Sent: Saturday, June 09, 2012 4:35 PM To: Evan Rempel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] is there a select for update insert if not exist type command? On Sat, 9 Jun 2012 15:41:34 -0700 Evan Rempel wrote: > I have a project where I will have two clients essentially doing the > same things at the same time. The idea is that if one has already done the > work, then the second one does not need to do it. > > I was hoping that adding a task related unique identifier to a table > could be used to coordinate these client, something like a primary key and > using > select for update. > > The challenge I have is during the initial insert. One of the two clients > will cause postgresql > to log an error, which I would rather avoid (just seems dirty). > > Here is the time line; > > Both clients A and B becomes aware to do a task > > Client A or client B issues the "select for update ... if not exist do > insert" type command > The other client gets blocked on the "select for update. > > First client finishes insert/updates to record that it has delt with the task > > second client gets unblocked and reads the record realizing that the first > client delt with the task already. > > > It is the "select for update ... if not exist do insert" type command that I > am ignorant of how to code. > > Anyone care to school me? It's amazing to me how often I have this conversation ... How would you expect SELECT FOR UPDATE to work when you're checking to see if you can insert a row? If the row doesn't exist, there's nothing to lock against, and thus it doesn't help anything. FOR UPDATE is only useful if you're UPDATING a row. That being given, there are a number of ways to solve your problem. Which one you use depends on a number of factors. If it's x number of processes all contending for one piece of work, you could just exclusive lock the entire table, and do the check/insert with the table locked. Thi
Re: [GENERAL] is there a select for update insert if not exist type command?
Depending on the version of Pg there are two possible solutions to this problem. The first (old solution) that really only works well one row at a time is to do a stored procedure that does something like: update foo set bar = baz where id = in_id if not found insert into foo (bar) values (baz) end if; The newer way, which can be done in SQL with Pg 9.1 is to use writable common table expressions. See http://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/ for an example by Vibhor Kumar. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a select for update insert if not exist type command?
One of the possible strategies that comes to my mind is: 1) Log your "syslog stream" into PostgreSQL database (no need to record all message, just a simple table the the event key and a flag "processed" field) 2) When problem event arrives, the first server should "select for update" on the event of syslog, then update a "processed" field (from "0" to "1" or false to true, something like that). When second server try to get the "select for update" on the events table, it will fails, and then it can move ahead in the log looking for other errors that need attention. I cannot say how adversely this strategy would affect your system (or if there are other contention involved, like page locks), but seems very logical for me. I've used this to distribute processing on users desktops for a massive processing system with success (but using MySQL for storing data). Regards, Edson Em 09/06/2012 23:40, Evan Rempel escreveu: Thanks for the input. Dave also replied indicating that without more details it is difficult the really help. I was intentionally vague to see if there was some SQL standard way like to mysql "insert ... on duplicate update ... " syntax, or the proposed MSSQL merge command. Since not, I'll give a lot more detail without writing a book. We are working on a project where a syslog stream arrives and is parsed in real time. Every log message is considered to be an "event". Events can usually be ignored because they are "normal behaviour". Some events indicate a problem and should create an incident. Repetitions of the same event should not create a new incident if the current incident is not resolved. For redundancy, two independent systems will consume and process these events. Now for the part where postgresql comes in. When an event that should create an incident is encountered, only one incident should be created. The incident details are not known until such time as the event occurs, so no pre-population of tables can occur. So only one of the two server should perform the insert, and then update it with details of successive events, possibly ticketing system identification, date/time of last pager message that went out. Once the incident is placed into postgresql, everything is easy, "select for update", determine all that should take place like paging, updating tickets, recording date/time of last alert sent to administrators. It is just that first insert that is the challenge. One system does the insert, only to have the other do the "select for update". I would like to have an insert that is locked AND visible to other sessions. Exclusive lock on the table is an idea, but it serializes ALL new incident creation, and we only NEED to serialize for the same incident identifier. Since both (all) of the systems that will be processing the live log stream, in all likelihood all of the servers will always be working on the same data and thus the same incident, so will always be locking the same piece of data anyway, so the full table lock may not be any worse. I was thinking of using and advisory lock, which would also serialize everything, just like a table lock, but again, that may not be a problem since all processes work on the same data at the same time anyways. I could also use a custom "my_locks" table that just has rows with unique values that I do a "select for update" on to serialize everything. Again, no functional difference from table or advisory locks. A follow on questions; Is there anything inherently atomic about a stored procedure? Does the stored procedure simply run within the transaction context of where it is called from? begin transaction - select storedProc1 - select storedProc2 commit Would the actions of both stored procedures would be a single atomic action? Thanks again for lending me your experience, it can, and is, saving me days. Evan. From: Bill Moran [wmo...@potentialtech.com] Sent: Saturday, June 09, 2012 4:35 PM To: Evan Rempel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] is there a select for update insert if not exist type command? On Sat, 9 Jun 2012 15:41:34 -0700 Evan Rempel wrote: I have a project where I will have two clients essentially doing the same things at the same time. The idea is that if one has already done the work, then the second one does not need to do it. I was hoping that adding a task related unique identifier to a table could be used to coordinate these client, something like a primary key and using select for update. The challenge I have is during the initial insert. One of the two clients will cause postgresql to log an error, which I would rather avoid (just seems dirty). Here is the time line; Both clients A and B becomes aware to do a task Client A or client B issues the "select for update ... if not exist do insert" type command The other client gets blocked on the "select for update. First client finishes in
Re: [GENERAL] New crosslinks in docs
On Sat, Jun 9, 2012 at 6:06 AM, Adrian Klaver wrote: > So when did the links to other versions of the docs get rolled out? > To whomever is responsible, thanks very much. Ooh. Must be pretty recent, I've not seen them before. Seconded - thank you! Web searches often bring up the old versions, glad to have quick links to the latest. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
