Re: [GENERAL] join between a table and function.
Thanks for every one for help. I got it to work. The reason i used a function is that it calculates the values/attributes from several tables in a pretty complex way. I tried to do this by a view first but couldn't do it. I think it's impossible. The function is always supposed to return only one record with many columns. These columns are used as attributes to the table rows. I know that I have a lot to learn in postgresql. Perhaps I someday figure out a better way to achieve this. Thanks -Lauri On Wed, Aug 17, 2011 at 5:57 AM, David Johnston wrote: > On Aug 16, 2011, at 14:29, Merlin Moncure wrote: > >> On Tue, Aug 16, 2011 at 8:33 AM, Harald Fuchs wrote: >>> In article >>> , >>> Lauri Kajan writes: >>> I have also tried: select *, getAttributes(a.id) from myTable a >>> That works almost. I'll get all the fields from myTable, but only a one field from my function type of attributes. myTable.id | myTable.name | getAttributes integer | character | attributes 123 | "record name" | (10,20) >>> What is the right way of doing this? >>> >>> If you want the attributes parts in extra columns, use >>> >>> SELECT *, (getAttributes(a.id)).* FROM myTable a >> >> This is not generally a good way to go. If the function is volatile, >> you will generate many more function calls than you were expecting (at >> minimum one per column per row). The best way to do this IMO is the >> CTE method (as david jnoted) or, if and when we get it, 'LATERAL'. >> > > From your statement is it correct to infer that a function defined as > "stable" does not exhibit this effect? More specifically would the function > only be evaluated once for each set of distinct parameters and the resulting > records(s) implicitly cached just like the CTE does explicitly? > > 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 > -- 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] Query regarding PostGre database
On 18/08/2011 11:52 AM, Navin Chandra wrote: Hi, I am an application developer, want to use ‘PostGre’ as backend. May I know what is the maximum possible number of concurrent users? Your acknowledgement will be highly appreciated. You seem to have replied to yourself. Eh? There's no such thing as "PostGre". You probably meant PostgreSQL or just Postgres. The maximum number of concurrent users depends on the hardware and the database workload. It is impossible to answer without more information. There is a difference between the maximum number of connections and the maximum number of actively working queries. You can have lots of connections, but much fewer actively running queries if you want the system to perform well. As a rule of thumb, we usually say that the number of connections that should be doing work is roughly (number of CPU cores) + (number of hard drives) ... but of course that's incredibly vague and depends a huge amount on your workload and hardware specifics. For example, in some workloads the hard drives don't matter at all, it's only the CPUs and amount of memory. If you find that your PostgreSQL instance cannot handle enough connections, you can add a connection pooler like PgPool-II, or use your application server's connection pooler. Using fewer connections and a pooler is usually faster than using lots of direct connections to PostgreSQL anyway. Perhaps you can explain a bit more about what you want to do so we can help you in more detail? -- Craig Ringer -- 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] Query regarding PostGre database
On Wed, Aug 17, 2011 at 9:52 PM, Navin Chandra wrote: > Hi, > > I am an application developer, want to use ‘PostGre’ as backend. May I know > what is the maximum possible number of concurrent users? > Your acknowledgement will be highly appreciated. FYI, we call it PostgreSQL or pgsql around here. The maximum number you can create is much more than the maximum number you like want to create. What are you looking at doing? Might connection pooling be a good match for that? I have session db servers that keep ~800 persistent connections open for small single table queries and they do just fine. I've tested thousands of connections with decent performance. However having lots of open connections can lead to "thundering herd" problems and is generally suboptimal. -- 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] Query regarding PostGre database
On 08/17/11 8:52 PM, Navin Chandra wrote: I am an application developer, want to use ‘PostGre’ as backend. Please note, there is no such thing as PostGre. There is PostgreSQL, sometimes called Postgres for short. May I know what is the maximum possible number of concurrent users? A better question is, whats the optimal number of concurrent query operations that can be executed simultaneously to achieve maximum throughput. Under most conditions, you'll get MORE queries per second total throughput if you only try and execute between N and 2*N queries at a time. where N is the number of CPU cores or hardware threads you have, assuming your storage subsystem has sufficient performance for your I/O workload. With connecting pooling, like pg_bouncer, you could have 1000s of concurrent users on a reasonable sized server, as long as most of them are idle most of the time. I don't typically allow more than 100 or so actual connections to a given SQL server if I can avoid it by the use of pooling. -- 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] Query regarding PostGre database
Hi, I am an application developer, want to use 'PostGre' as backend. May I know what is the maximum possible number of concurrent users? Your acknowledgement will be highly appreciated. Thanks & Regards, Navin Pandit Gurgaon- 122 001 (India) From: selenama...@gmail.com [mailto:selenama...@gmail.com] On Behalf Of Selena Deckelmann Sent: Wednesday, August 17, 2011 9:52 PM To: Navin Chandra Subject: Re: Query regarding PostGre database Hi! Please direct this question to pgsql-general@postgresql.org I help run User Groups. -selena On Wed, Aug 17, 2011 at 5:52 AM, Navin Chandra wrote: Dear Sir/Madam, I am an application developer, want to use 'PostGre' as backend. May I know what is the maximum possible number of concurrent users? Your acknowledgement will be highly appreciated. Thanks & Regards, Navin C. Pandit IL&FS Technologies Ltd. Error! Filename not specified. | I T Services 3rd Floor, Ambience Corporate Tower, Ambience Mall, Ambience Island, NH -8 Gurgaon-122001 (HR) , INDIA TEL.: (+91) 124 4716475 -- http://postgresql.org Me - http://chesnok.com
Re: [GENERAL] Using Postgresql as application server
On Wed, Aug 17, 2011 at 9:38 PM, Sim Zacks wrote: > The point was not whether I have a bug in an external application, the point > is that I need an external application which creates more overhead and > another point of failure in the application stack. > 1) Not sure how an external python script is different from a PL/Python sproc except that the former exists external to transaction control. 2) there is absolutely no reason you can't build redundancy into this system. 3) The overhead really shouldn't be bad, and if your parts are well-modularized, and carefully designed overhead really should be minimal. I don;t see what you gain from using cron that you don't gain from using a persistent process and notify... you could even have a cron script to check if it is running and start if not from time to time. 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] Using Postgresql as application server
On 08/17/2011 05:34 PM, Scott Ribe wrote: On Aug 17, 2011, at 1:05 AM, Sim Zacks wrote: One problem we have with LISTEN/NOTIFY (and I haven't found the cause for this yet) is every once in a while my daemon stops listening. It may be after a month of use or longer, and may be caused by the database being restarted or something similar. When the daemon stops listening, it doesn't give any errors or indication that it isn't working anymore. So your daemon has a bug. When the database is restarted, connections will be closed, and the daemon should certainly notice that. Of course the cause may be something else, but either way I doubt it's a problem with NOTIFY/LISTEN. The point was not whether I have a bug in an external application, the point is that I need an external application which creates more overhead and another point of failure in the application stack.
Re: [GENERAL] Using Postgresql as application server
On 08/17/2011 06:13 PM, Chris Travers wrote: On Tue, Aug 16, 2011 at 11:53 PM, Sim Zacks wrote: We are doing this same sort of thing now. If the transaction goes through, the email record gets written to a table. We have a cron job that calls a database function that processes all emails that have not been processed yet. If the transaction gets rolled back, the email record does not get written to the table and the email does not get sent. In your scenario, if you send the NOTIFY message and then you roll back the transaction, the helper application will still send the email. IOW, doing this outside of the database can more easily break your transactional integrity. Notify hits on commit, right? Best Wishes, Chris Travers My bad. I just tested this. Notify doesn't get send until after commit.
Re: [GENERAL] Type casting text to Numeric - Query Error
Dear Pavel,
Thank you. Now it is not possible to use separate columns. The same works at
MYSQL with out casting. But here it is so. We have used CAST and works fine.
Thank you
vikram
From: Pavel Stehule
To: Vikram A
Cc: PGSQL - Genearal
Sent: Wednesday, 17 August 2011 6:00 PM
Subject: Re: [GENERAL] Type casting text to Numeric - Query Error
Hello
2011/8/17 Vikram A :
> Hi there,
> I have the following definitions,
> 1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL, SampleText character
> varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
> 2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
> Alex'),('1000'),('2500'),('555');
> 3. select sum(SampleText) as SampleText from Sampletemp;
> ERROR: function sum(character varying) does not exist
> LINE 3: select sum(SampleText) as SampleText from Sampletemp;
> ^
> HINT: No function matches the given name and argument types. You may need
> to add explicit type casts.
> 4. select sum(SampleText :: int) as SampleText from Sampletemp;
> ERROR: invalid input syntax for integer: "Mr. Raja"
> I am getting error while selecting using some aggregation.
> I would like to sum up these values, if it has TEXT (example Name)that can
> be ZERO while querying. I need answer as 4055.
> Can i have your suggestion/solutions please?
SELECT sum(CASE WHEN sampletext ~ e'^\\d+$' THEN sampletext::int ELSE
0 END) FROM ..
Regards
Pavel Stehule
p.s. better to use a two columns
>
> Regards,
> Vikram A
Re: [GENERAL] streaming replication: one problem & several questions
On Thu, Aug 18, 2011 at 4:26 AM, Lonni J Friedman wrote: > I wish I knew. All the documentation out there always focuses on > setting up a restore command, as if there would be a huge disaster if > it wasn't done. Is it safe to simply make wal_keep_segments really > large, and skip the restore_command altogether? There are pros and cons of replication setting NOT using restore_command. Please evaluate whether it's safe or not according to them. Pros; * You don't need to prepare the archive area shared between the master and standby. Don't need to purchase new server for that. * If you use restore_command and have the shared archive area, archive_command is a bit more likely to fail because it copies WAL files via network. Failure of archive_command might fill up the pg_xlog directory on the master, which might cause PANIC error. So you need to consider how to handle this failure case. OTOH, you don't need to do that if you don't use restore_command. Cons; * When setting up the standby, if the backup takes very long because the database is quite large, some WAL files required to the backup might be deleted from the master during the backup. If this happens, the standby starting from that backup will fail to start replication. To avoid such an unexpected deletion of WAL files from the master, you need to increase wal_keep_segments enough. But it might not be easy to determine the appropriate value of it. * You need to prepare large disk space for pg_xlog directory if wal_keep_segments is large. Because, in that case, a large number of WAL files can accumulate in pg_xlog. * When replication connection is terminated, no WAL data is streamed to the standby, so the standby cannot advance recovery at all. OTOH, if you set restore_command on the standby and have the shared archive area, the standby can read new WAL file from it by using restore_command and advance recovery. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On 18/08/2011 12:35 AM, John R Pierce wrote: On 08/17/11 7:40 AM, Merlin Moncure wrote: GRANT/REVOKE only constrain read/write privileges to a database. at a table level, and even distinguishing between INSERT (writing new data) and UPDATING (updating existing data). Column level, actually :-) and they can control read access as well as write access. Further control for writes can be applied using triggers that RAISE EXCEPTION when they don't like something. -- Craig Ringer -- 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] Failover architecture
> Of course, Is there any easy way to do that? If so, then what > happens when pgpool tries forward an INSERT to the master while > it's in read-only mode? Assuming "read-only mode" is a database running in read-only transaction mode(like standby), you will get errors something like this: ERROR: cannot execute INSERT in a read-only transaction (For the record, I'm pretty sure that > there isn't any easy or obvious way to make a database > read-only, and that we can simulate read-only mode by adding > INSERT/UPDATE triggers on each of the four -- yes, only four -- > tables in the database, silently ignoring data that's posted. I > floated this with the project managers, and they were OK with > this idea -- but I wanted to double-check whether this is a > viable solution, or if there's an obvious pitfall I'm missing > and/or a better way to go about this. > If we use master-slave replication, and communication is cut > off, does the slave reconnect automatically? I believe that the > answer is "yes," and that the replication will continue so long Yes, as long as you turn on archive logging *and* keep enough archive log segments. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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: one problem & several questions
On Mon, Aug 15, 2011 at 9:34 PM, Fujii Masao wrote: > On Thu, Aug 11, 2011 at 7:19 AM, Lonni J Friedman wrote: >> First the problem. On *only* one of the two standby servers, I'm >> seeing errors like the following whenever I issue any SQL commands on >> the master which write (insert, update, etc) to the database: >> LOG: invalid record length at 8/7A20 >> FATAL: terminating walreceiver process due to administrator command >> LOG: invalid record length at 8/7AB0 >> LOG: streaming replication successfully connected to primary >> LOG: invalid record length at 8/7B20 >> FATAL: terminating walreceiver process due to administrator command >> LOG: record with zero length at 8/7BB0 >> LOG: streaming replication successfully connected to primary >> LOG: record with incorrect prev-link 8/7958 at 8/7DB0 >> LOG: streaming replication successfully connected to primary > > Did you use gcc4.6 or later to build PostgreSQL9.0? If yes, you would > face the same problem reported before; > http://archives.postgresql.org/pgsql-hackers/2011-06/msg00661.php > > This problem was fixed, and the fix will be included in next minor update > (i.e., 9.0.5). > http://archives.postgresql.org/pgsql-committers/2011-06/msg00101.php Yes, that was the issue. I thought that I had replied earlier to someone else speculating that this was the issue, but perhaps I had not. >> 1) Both of the wiki links above comment that the restore_command may >> not be necessary if wal_keep_segments is large enough (mine is set to >> 128). I was going to setup the restore_command anyway, as I'm not yet >> confident enough about streaming replication and failover with >> postgresql to take chances, although the fact that i have two standby >> servers makes this setup a bit more complex. However, can anyone >> comment about whether its ever truly safe 100% of the time to run >> without a restore_command ? > > Specifically, what problem are you concerned about? I wish I knew. All the documentation out there always focuses on setting up a restore command, as if there would be a huge disaster if it wasn't done. Is it safe to simply make wal_keep_segments really large, and skip the restore_command altogether? -- 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] Not Seeing Syntax Error
On Wed, 17 Aug 2011, David Johnston wrote: To be honest I was too and maybe I somehow implied that to you. Anyway, I believe if you are dealing with CSV import then you are correct but apparently SQL is not as forgiving. I use a third-party application to import my CSV usually so whether that observation applies to PSQL or other mechanisms (like pg_loader) I cannot say. David, It may well be different between COPY and INSERT INTO. Anywho, I altered the column and set the default to NULL. That works. Regards, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On 08/17/11 7:40 AM, Merlin Moncure wrote: GRANT/REVOKE only constrain read/write privileges to a database. at a table level, and even distinguishing between INSERT (writing new data) and UPDATING (updating existing data). you can get even finer granularity, using functions with SECURITY_DEFINER based permissions. -- 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] Not Seeing Syntax Error
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard Sent: Wednesday, August 17, 2011 12:27 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Not Seeing Syntax Error On Wed, 17 Aug 2011, Thom Brown wrote: > The error message points to the problem. No value, not even NULL, has > been specified for 5th column. Either put DEFAULT or NULL in there. > You can't put nothing. I was under the impression (obviously wrong) that a blank field was accepted as a NULL. Thanks, Rich -- To be honest I was too and maybe I somehow implied that to you. Anyway, I believe if you are dealing with CSV import then you are correct but apparently SQL is not as forgiving. I use a third-party application to import my CSV usually so whether that observation applies to PSQL or other mechanisms (like pg_loader) I cannot say. 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] Using Postgresql as application server
On Wed, Aug 17, 2011 at 7:40 AM, Merlin Moncure wrote: > > GRANT/REVOKE only constrain read/write privileges to a database. > Application level security is typically much finer grained than that. > Also, I using SQL roles for actual user roles is not typically done > for various reasons. Generally, SQL roles are used to define 'what' > is logging in, no necessarily 'who'. If you allow and SQL through > from the application then table level security becomes very > important...otherwise not so much. You can use roles to affect things on a fairly granular level, if you combine a relation interface with a functional one. And the fact most people use SQL roles this way is due to enforcing security in the middleware. The disadvantage is that the database has to trust the middleware and the other clients connecting to it. To some extent this is unavoidable, but in general, reducing the level of trust between the components reduces the security exposure. Obviously this has some issues in terms of how far it can scale. One of the things we decided to do with LedgerSMB was to make every application user a database user and then enforce security on the back end. > >> What I am saying is that the further back you enforce the security the >> more you can guarantee consistent enforcement across applications. >> Connection pooling makes this much harder because you can't enforce it >> within the db using the normal methods and end up having to implement >> it all over. Instead you have to implement security before the data >> hits the database. That's a big difference and it has HUGE >> ramifications for security exposure vs utility of an application. > > That is totally incorrect. pgbouncer maintains separate pools for > each role and only intermingles queries for like roles. Any > intelligent connection pooler would do the same. You lose access to > database session features but database level security features are > still enforced. Whether you connection pool or not really doesn't > play into this from my point of view. Right, but then you still can't enforce *user* permissions on the database because there isn't a point in having a connection pool if each user gets one as a db user, is there? > > Recall that in our hypothetical 'database as middleware' database, the > main tables and there data are not actually in the database -- the > only tables available to query would be session state, etc. Most > operations would funnel through back to the main database through > procedures and application level security would be checked there. > Now, if you want your system to be simple, tight, and fast, you could > combine those two databases but would have to figure out how to manage > security to a libpq speaking application. Like I said, in my case I > did this with a whitelist, but it's not the only way. I guess I am approaching it differently as looking at logical tiers getting incorporated into the RDBMS, which becomes the centerpiece of and entrance point to the application server environment. That's why I am talking about the database taking over traditional middleware functions rather than having a separate database.. > >>> well, not exactly. it is a concession to security. allowing > If you expose (as I did) your middleware api as a 100% sql function > interface, then yes ad hoc sql is not allowed. If you wrap your > middleware with a http server than ad hoc sql would not be allowed. I > doubt the day will come where the browser will be sending ad hoc SQL > queries directly to a database. One of my LedgerSMB customers decided they wanted to be able to distribute SQL scripts to bookkeepers and have them run them via pgAdmin. So from the browser? No. From other software clients? Quite possibly. What we were able to do was assign the specifically needed functionality to the pgAdmin users and thus ensure that security and data integrity were not compromised by this approach. Now, the users in this case require a lot of read access, with a few very specific write permissions. The security footprint here is very low. We couldn't have accommodated that request safely, however, if our permissions system wasn't geared around the db enforcing permissions per user. > > The reason to use a database backend to handle middleware functions is > based on the strength of the SQL language, supported by the various PL > extensions you may want to include, to manage various everyday > programming tasks. The security discussion is a bit of a sideshow > because it is generally a tiny fraction of the coding that typically > happens at this level. An individual's personal appraisal of this > idea will largely depend on certain personal factors that will vary > from developer to developer. An unbiased analysis would probably > conclude that it is an interesting, but unproven approach with a lot > of potential. It's a tiny piece of the code, but it's a critical one, and when something goes wrong
Re: [GENERAL] Not Seeing Syntax Error
On Wed, 17 Aug 2011, Thom Brown wrote: The error message points to the problem. No value, not even NULL, has been specified for 5th column. Either put DEFAULT or NULL in there. You can't put nothing. I was under the impression (obviously wrong) that a blank field was accepted as a NULL. Thanks, Rich -- 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] Failover architecture
I can't help so much with the Pg replication specific parts, but this I can answer: On 17/08/2011 9:25 PM, Reuven M. Lerner wrote: restart streaming, it goes back into read-write mode. Is there a way (other than Bucardo, which doesn't seem to fit the bill for this project), is there any way for us to merge whatever diffs might be on the two servers, and then reconnect them in master-slave streaming mode when communication is re-established? Nope. Merging diffs between two "forked" database timelines is not possible with PostgreSQL's built-in replication. Pg does replication at the block level, so there's no meaningful way to merge the changes. Even if replication were done at the tuple level, how would you merge changes where both forks INSERTed into a table with a sequence-generated primary key? Or used an aggregate like sum(...) when generating content for a new record? Statement-level replication has similar issues. An app may calculate a value that it includes in a query based on the result of a prior query or might issue a different query depending on prior queries. This makes it impossible to interleave and replay recorded statements when contact is resumed and still get consistent, correct results. It's a lot like the SERIALIZABLE transaction problem on a larger scale. Often you can run two transactions in parallel and have them produce the same results as they would've done when run serially. It's not possible to guarantee this (without predicate locking and communication between the transactions) though, which is why apps must be prepared for serializable transactions to fail. Same deal when merging timelines, except that you're dealing with long-committed transactions the app _trusts_ the database to have successfully recorded. The only way to do this sort of thing seems to be at the application level. You can insert new keys with UUIDs to work around sequence issues, etc, but you'll still have to handle delete collisions and numerous other issues yourself. No-SQL folks may chime in with " magically fixes this" here, but all the cases I've seen so far just push the problem back to the application to deal with rather than finding a true solution for seamlessly merging forked timelines. I suspect the only sane way to cope with these issues _reliably_ will be to have your app _always_ run with the assumption that the other server is unreachable, and always be synchronizing with the other server as it goes. Otherwise you'll find that everything works great until your link goes down, then it'll turn out that your clever merge-and-sync logic has bugs that eat your data. Of course, you'll probably find that your DB access logic becomes cumbersome and painful... I can't help thinking that there must be some easy solution to this, but I've never seen anyone solve the DB change merging problem properly. Everyone who claims to turns out to have a "solution" with numerous caveats and limitations - or numerous obvious flaws. Once you fork a timeline where events may depend on the outcome of prior events, you cannot guarantee that you can seamlessly merge them into a single timeline where every event happens (or doesn't happen) in the same order as it would've without the fork. -- Craig Ringer -- 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] Failover architecture
On 08/17/11 6:25 AM, Reuven M. Lerner wrote: * Once the slave has been promoted to master, we have a single server, and a single point of failure. Is there any simple way to get the former master to become a slave? I assume that it would need to start the whole becoming-a-slave process from scratch, invoking pg_start_backup(), copying files with rsync, and then pg_stop_backup(), followed by connecting to the new master. But perhaps there's a shorter, easier way for a "fallen master" to become a slave? nope, thats pretty much what you have to do.if you use rsync, and the files haven't changed too much, the replication should be relatively fast. * Is there any easy, straightforward way for a "fallen master" to re-take its position, demoting the promoted slave back to its original position of slave? (With little or no downtime, of course.) I assume not, but I just wanted to check; my guess is that you have to just make it a slave, and then start to follow the newly promoted master. what you said. * If the network connection between the two data centers goes down, but if the computers are still up, we worry that we'll end up with two masters -- the original master, as well as the slave, which will (falsely) believe the master to be down, and will thus promote itself to master. Given that PostgreSQL doesn't allow master-master synchronization, we're thinking of using a heartbeat to check if the other computer is available, in both directions -- and that if the master cannot detect the slave, then it goes into a read-only mode of some sort. Then, when it detects the slave again, and can restart streaming, it goes back into read-write mode. Is there a way (other than Bucardo, which doesn't seem to fit the bill for this project), is there any way for us to merge whatever diffs might be on the two servers, and then reconnect them in master-slave streaming mode when communication is re-established? problematic in any sort of cluster system, you end up with two versions of 'the truth' and you have to figure out how to reconcile them. absolutely won't work at all with streaming replication, which requires the two servers to be block by block the same.If you have to deal with this sort of thing, you may want to do your OWN replication at an application level, perhaps using some sort of messaging environment, where you can queue up the pending "change requests" * Of course, Is there any easy way to do that? If so, then what happens when pgpool tries forward an INSERT to the master while it's in read-only mode? (For the record, I'm pretty sure that there isn't any easy or obvious way to make a database read-only, and that we can simulate read-only mode by adding INSERT/UPDATE triggers on each of the four -- yes, only four -- tables in the database, silently ignoring data that's posted. I floated this with the project managers, and they were OK with this idea -- but I wanted to double-check whether this is a viable solution, or if there's an obvious pitfall I'm missing and/or a better way to go about this. that sounds messy. * If we use master-slave replication, and communication is cut off, does the slave reconnect automatically? I believe that the answer is "yes," and that the replication will continue so long as we're in the defined window for replication delays. -- 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] Connection Error during Pg_restore
On 17/08/2011 7:02 PM, Rebecca Clarke wrote: Hi there I'm doing a restore of a large table. The backup file is 18gb. When I run the restore after sometime it comes up with this error while it is restoring the data. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3022; 0 4287406059 TABLE DATA tbl_exampletable postgres pg_restore: [archiver (db)] COPY failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. You'll need to look at the server logs to see why the server terminated the connection. I expect you'll see a backend crash, but it's hard to be sure. Please also supply your version and the other basic information listed here: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_you_need_to_mention_in_problem_reports -- Craig Ringer -- 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] Not Seeing Syntax Error
On 17 August 2011 16:49, Rich Shepard wrote:
> For several INSERT INTO rows I get a syntax error when the quant column is
> NULL for one specific parameter. I don't see my error. Here is an example
> row:
>
> psql:insert.sql:8: ERROR: syntax error at or near ","
> LINE 1: ...ALUES ('9609-0759','BC-1.5','1996-09-19','**Arsenic',,'mg/L');
> ^
> The source line is:
>
> INSERT INTO chemistry (lab_nbr, loc_name, sample_date, param, quant,
> units)VALUES ('9609-0759','BC-1.5','1996-09-19','**Arsenic',,'mg/L');
>
> and the quant column is defined as type real. There are numerous other rows
> where quant IS NULL.
>
> What have I missed?
>
The error message points to the problem. No value, not even NULL, has been
specified for 5th column. Either put DEFAULT or NULL in there. You can't
put nothing.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
[GENERAL] Not Seeing Syntax Error
For several INSERT INTO rows I get a syntax error when the quant column is
NULL for one specific parameter. I don't see my error. Here is an example
row:
psql:insert.sql:8: ERROR: syntax error at or near ","
LINE 1: ...ALUES ('9609-0759','BC-1.5','1996-09-19','Arsenic',,'mg/L');
^
The source line is:
INSERT INTO chemistry (lab_nbr, loc_name, sample_date, param, quant,
units)VALUES ('9609-0759','BC-1.5','1996-09-19','Arsenic',,'mg/L');
and the quant column is defined as type real. There are numerous other rows
where quant IS NULL.
What have I missed?
Rich
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On Tue, Aug 16, 2011 at 11:53 PM, Sim Zacks wrote: > We are doing this same sort of thing now. If the transaction goes through, > the email record gets written to a table. We have a cron job that calls a > database function that processes all emails that have not been processed > yet. If the transaction gets rolled back, the email record does not get > written to the table and the email does not get sent. > In your scenario, if you send the NOTIFY message and then you roll back the > transaction, the helper application will still send the email. IOW, doing > this outside of the database can more easily break your transactional > integrity. > Notify hits on commit, right? 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] Using Postgresql as application server
On Tue, Aug 16, 2011 at 6:14 PM, Chris Travers wrote: > On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure wrote: > >> >> /shrug. pretty much every project I've ever worked on application >> security has been ad hoc, database driven, not very complicated, and >> not a performance bottleneck. By the way, I think the opposite of >> you: security information relating to application roles and actions >> *should* be stored in the database (it is, after all, data) even if it >> is enforced by a classic middleware. What happens when some other >> application, written by another team, connects to the database? > > Not understanding my perspective. Ideally you'd use the RDBMS's > functionality directly to enforce security via GRANT and REVOKE > statements. Whether it is stored in the database or not is for the > RDBMS to decide. GRANT/REVOKE only constrain read/write privileges to a database. Application level security is typically much finer grained than that. Also, I using SQL roles for actual user roles is not typically done for various reasons. Generally, SQL roles are used to define 'what' is logging in, no necessarily 'who'. If you allow and SQL through from the application then table level security becomes very important...otherwise not so much. > What I am saying is that the further back you enforce the security the > more you can guarantee consistent enforcement across applications. > Connection pooling makes this much harder because you can't enforce it > within the db using the normal methods and end up having to implement > it all over. Instead you have to implement security before the data > hits the database. That's a big difference and it has HUGE > ramifications for security exposure vs utility of an application. That is totally incorrect. pgbouncer maintains separate pools for each role and only intermingles queries for like roles. Any intelligent connection pooler would do the same. You lose access to database session features but database level security features are still enforced. Whether you connection pool or not really doesn't play into this from my point of view. Recall that in our hypothetical 'database as middleware' database, the main tables and there data are not actually in the database -- the only tables available to query would be session state, etc. Most operations would funnel through back to the main database through procedures and application level security would be checked there. Now, if you want your system to be simple, tight, and fast, you could combine those two databases but would have to figure out how to manage security to a libpq speaking application. Like I said, in my case I did this with a whitelist, but it's not the only way. >> well, not exactly. it is a concession to security. allowing >> untrusted entities to send ad hoc sql to a database is obviously not >> going to fly so it must be dealt with appropriately. note pgbouncer >> (or node.js etc) is not defining or handling session auth, just >> playing a small role enforcement. an auth'd application service >> requests are essentially protocol noise and I see no problem letting >> the protocol handler bounce them out. also, whatever you happen to >> wrap your 'middleware' database is still part of the middleware. > > Well, what you are actually doing here is enforcing security on a > level of abstraction away from the database. This means that you > can't allow ad hoc queries because you can't guarantee safety. I > don't know what you get by doing this instead of providing > interface-level security in the part of your middleware. In fact > that's essentially what you have to do, is it not? If you expose (as I did) your middleware api as a 100% sql function interface, then yes ad hoc sql is not allowed. If you wrap your middleware with a http server than ad hoc sql would not be allowed. I doubt the day will come where the browser will be sending ad hoc SQL queries directly to a database. The reason to use a database backend to handle middleware functions is based on the strength of the SQL language, supported by the various PL extensions you may want to include, to manage various everyday programming tasks. The security discussion is a bit of a sideshow because it is generally a tiny fraction of the coding that typically happens at this level. An individual's personal appraisal of this idea will largely depend on certain personal factors that will vary from developer to developer. An unbiased analysis would probably conclude that it is an interesting, but unproven approach with a lot of potential. >> also I think most people would not go the libpq route even though >> listen/notify allows you to create beautifully interactive systems -- >> mainly due to weirdness of the whole thing and the amount of work you >> have to do to get it safe. > > Why? Each listener has to have its own connection, right? Otherwise > there is nowhere to send the notifications to. That connection has t
Re: [GENERAL] Using Postgresql as application server
On Aug 17, 2011, at 1:05 AM, Sim Zacks wrote: > One problem we have with LISTEN/NOTIFY (and I haven't found the cause for > this yet) is every once in a while my daemon stops listening. It may be after > a month of use or longer, and may be caused by the database being restarted > or something similar. When the daemon stops listening, it doesn't give any > errors or indication that it isn't working anymore. So your daemon has a bug. When the database is restarted, connections will be closed, and the daemon should certainly notice that. Of course the cause may be something else, but either way I doubt it's a problem with NOTIFY/LISTEN. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On Aug 17, 2011, at 12:53 AM, Sim Zacks wrote: > In your scenario, if you send the NOTIFY message and then you roll back the > transaction, the helper application will still send the email. How? NOTIFY doesn't get delivered until the transaction commits. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stored procedure name
Hi, Is there any way to get current stored procedure name? Best Regards,
Re: [GENERAL] How to install PGCRYPTO in PostgreSQL9
Thanks for the reply. I have installed with the user "postgres" and it worked. Thanks! 2011/8/17 Adrian Klaver : > On Wednesday, August 17, 2011 6:44:31 am Andre Lopes wrote: >> I have installed and tried to import the SQL, but I got this: >> >> [code] >> [andre@andre public]$ psql -d 420 -f >> /usr/pgsql-9.0/share/contrib/pgcrypto.sql SET > >> psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:197: ERROR: permission >> denied for language c >> psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:202: ERROR: permission >> denied for language c >> [andre@andre public]$ >> [/code] >> >> What's wrong here? > > Would seem to indicate you did not install as database superuser. > >> > > > -- > 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
Re: [GENERAL] How to install PGCRYPTO in PostgreSQL9
On Wednesday, August 17, 2011 6:44:31 am Andre Lopes wrote: > I have installed and tried to import the SQL, but I got this: > > [code] > [andre@andre public]$ psql -d 420 -f > /usr/pgsql-9.0/share/contrib/pgcrypto.sql SET > psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:197: ERROR: permission > denied for language c > psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:202: ERROR: permission > denied for language c > [andre@andre public]$ > [/code] > > What's wrong here? Would seem to indicate you did not install as database superuser. > -- 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
Re: [GENERAL] How to install PGCRYPTO in PostgreSQL9
I have installed and tried to import the SQL, but I got this: [code] [andre@andre public]$ psql -d 420 -f /usr/pgsql-9.0/share/contrib/pgcrypto.sql SET psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:9: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:14: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:19: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:24: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:29: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:34: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:39: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:44: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:49: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:54: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:59: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:64: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:72: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:77: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:85: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:90: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:98: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:103: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:111: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:116: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:124: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:129: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:137: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:142: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:150: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:155: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:163: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:168: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:176: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:181: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:189: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:197: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:202: ERROR: permission denied for language c [andre@andre public]$ [/code] What's wrong here? Best Regards, 2011/8/17 Devrim GÜNDÜZ : > On Wed, 2011-08-17 at 06:28 -0700, Andre Lopes wrote: >> >> I am using CentOS and PostgreSQL9. I have an application that uses >> Pgcrypto. I have googled but I am not sure how can I install this in >> PostgreSQL9. > > If you are using RPMS, then install -contrib RPM, and then install > pgcrypto using pgcrypto.sql that comes with the package. > > If it is the source installation, run make install under > contrib/pgcrypto directory. Then again, load pgcrypto.sql to your > database. > > Regards, > -- > Devrim GÜNDÜZ > Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to install PGCRYPTO in PostgreSQL9
On Wed, 2011-08-17 at 06:28 -0700, Andre Lopes wrote: > > I am using CentOS and PostgreSQL9. I have an application that uses > Pgcrypto. I have googled but I am not sure how can I install this in > PostgreSQL9. If you are using RPMS, then install -contrib RPM, and then install pgcrypto using pgcrypto.sql that comes with the package. If it is the source installation, run make install under contrib/pgcrypto directory. Then again, load pgcrypto.sql to your database. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] How to install PGCRYPTO in PostgreSQL9
Hi, I am using CentOS and PostgreSQL9. I have an application that uses Pgcrypto. I have googled but I am not sure how can I install this in PostgreSQL9. Someone can give me a clue on this? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Failover architecture
Hi, everyone. I'm working on a project that is already using PostgreSQL 9.0, including streaming replication. I'm trying to help them figure out a good architecture for ensuring stability and failover under a variety of conditions, and wanted to ask the community for suggestions and help. Basically, they have a mission-critical application that talks to PostgreSQL, and which works quite well. Because of the mission-critical nature of the application, it has been implemented twice, once at each data center. The two data centers are connected via a network connection; one PostgreSQL server acts as the master, and the other acts as a (read-only) slave. We're using pgpool in the second data center (i.e., the one with the PostgreSQL replication slave) to send all writes to the first data center (i.e., the one with the PostgreSQL replication master), but to balance reads across the two servers. This all works really well. The automatic failover also works well, such that when the master goes down, the slave is promoted to the master, a bit of IP-address switching happens behind the scenes, and things continue to hum along. So far, so good. But we have a few questions: Once the slave has been promoted to master, we have a single server, and a single point of failure. Is there any simple way to get the former master to become a slave? I assume that it would need to start the whole becoming-a-slave process from scratch, invoking pg_start_backup(), copying files with rsync, and then pg_stop_backup(), followed by connecting to the new master. But perhaps there's a shorter, easier way for a "fallen master" to become a slave? Is there any easy, straightforward way for a "fallen master" to re-take its position, demoting the promoted slave back to its original position of slave? (With little or no downtime, of course.) I assume not, but I just wanted to check; my guess is that you have to just make it a slave, and then start to follow the newly promoted master. If the network connection between the two data centers goes down, but if the computers are still up, we worry that we'll end up with two masters -- the original master, as well as the slave, which will (falsely) believe the master to be down, and will thus promote itself to master. Given that PostgreSQL doesn't allow master-master synchronization, we're thinking of using a heartbeat to check if the other computer is available, in both directions -- and that if the master cannot detect the slave, then it goes into a read-only mode of some sort. Then, when it detects the slave again, and can restart streaming, it goes back into read-write mode. Is there a way (other than Bucardo, which doesn't seem to fit the bill for this project), is there any way for us to merge whatever diffs might be on the two servers, and then reconnect them in master-slave streaming mode when communication is re-established? Of course, Is there any easy way to do that? If so, then what happens when pgpool tries forward an INSERT to the master while it's in read-only mode? (For the record, I'm pretty sure that there isn't any easy or obvious way to make a database read-only, and that we can simulate read-only mode by adding INSERT/UPDATE triggers on each of the four -- yes, only four -- tables in the database, silently ignoring data that's posted. I floated this with the project managers, and they were OK with this idea -- but I wanted to double-check whether this is a viable solution, or if there's an obvious pitfall I'm missing and/or a better way to go about this. If we use master-slave replication, and communication is cut off, does the slave reconnect automatically? I believe that the answer is "yes," and that the replication will continue so long as we're in the defined window for replication delays. Thanks for any suggestions and answers that you can provide. And of course, if I've missed something obvious in the documentation, then a pointer to the appropriate resource would be more than welcome.n Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner
Re: [GENERAL] Type casting text to Numeric - Query Error
On 17/08/2011 13:17, Vikram A wrote:
> Hi there,
>
> I have the following definitions,
>
> 1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL, SampleText
> character varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
>
> 2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
> Alex'),('1000'),('2500'),('555');
>
> 3. select sum(SampleText) as SampleText from Sampletemp;
>
> ERROR: function sum(character varying) does not exist
> LINE 3: select sum(SampleText) as SampleText from Sampletemp;
> ^
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> 4. select sum(SampleText :: int) as SampleText from Sampletemp;
> ERROR: invalid input syntax for integer: "Mr. Raja"
Recent versions of PostgreSQL are much pickier about types - certain
implicit casts were removed, most notably text to numeric types.
In any case, SUM makes no sense for character values. You'd be better
off using two separate columns for the text and numeric values.
> I would like to sum up these values, if it has TEXT (example Name)that
> can be ZERO while querying. I need answer as 4055.
Try using a CASE expression inside the SUM to test for non-numeric values.
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] Type casting text to Numeric - Query Error
Hello
2011/8/17 Vikram A :
> Hi there,
> I have the following definitions,
> 1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL, SampleText character
> varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
> 2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
> Alex'),('1000'),('2500'),('555');
> 3. select sum(SampleText) as SampleText from Sampletemp;
> ERROR: function sum(character varying) does not exist
> LINE 3: select sum(SampleText) as SampleText from Sampletemp;
> ^
> HINT: No function matches the given name and argument types. You may need
> to add explicit type casts.
> 4. select sum(SampleText :: int) as SampleText from Sampletemp;
> ERROR: invalid input syntax for integer: "Mr. Raja"
> I am getting error while selecting using some aggregation.
> I would like to sum up these values, if it has TEXT (example Name)that can
> be ZERO while querying. I need answer as 4055.
> Can i have your suggestion/solutions please?
SELECT sum(CASE WHEN sampletext ~ e'^\\d+$' THEN sampletext::int ELSE
0 END) FROM ..
Regards
Pavel Stehule
p.s. better to use a two columns
>
> Regards,
> Vikram A
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Type casting text to Numeric - Query Error
Hi there,
I have the following definitions,
1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL, SampleText character
varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
Alex'),('1000'),('2500'),('555');
3. select sum(SampleText) as SampleText from Sampletemp;
ERROR: function sum(character varying) does not exist
LINE 3: select sum(SampleText) as SampleText from Sampletemp;
^
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
4. select sum(SampleText :: int) as SampleText from Sampletemp;
ERROR: invalid input syntax for integer: "Mr. Raja"
I am getting error while selecting using some aggregation.
I would like to sum up these values, if it has TEXT (example Name)that can be
ZERO while querying. I need answer as 4055.
Can i have your suggestion/solutions please?
Regards,
Vikram A
Re: [GENERAL] Change master to standby
Thank you for the answer! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Change-master-to-standby-tp4703925p4707810.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Change master to standby
On Tue, Aug 16, 2011 at 6:55 PM, Alexander Perepelica wrote: > Can I change server mode master to slave (standby) without restarting? Which replication tool do you use? If streaming replication, the answer is "No". You need to shutdown the master, make a fresh base backup from new master, create recovery.conf and start the server as the standby from the backup. I have no idea about other replication method. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connection Error during Pg_restore
Hi there I'm doing a restore of a large table. The backup file is 18gb. When I run the restore after sometime it comes up with this error while it is restoring the data. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3022; 0 4287406059 TABLE DATA tbl_exampletable postgres pg_restore: [archiver (db)] COPY failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. WARNING: errors ignored on restore: 1 It will work if I use COPY but the table is so big I need to use pg_dump to compress it. Any help would be great. Thanks Rebecca
[GENERAL] Run server recovery
Hi! Can I on server which is master (already work) run recovery mode: 1. start server 2. change recovery.conf 3. reload config ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Run-server-recovery-tp4707673p4707673.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Run server recovery
Or recovery mode start only when server load? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Run-server-recovery-tp4707673p4707685.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to write a psql command inside a function?
Hi Chris, I don't have super-user. So I knew I can go with only \COPY command. I want to export the data from a table to a .CSV file. It worked fine when this command is used as a stand-alone. Now, I want to embed this line inside a function. Is there any alternative way for my requirement? Please guide me. John has proposed to see COPY to STDOUT which I am not aware of, and started looking into it. However, the operations I said above have suppose to be done only on the database side! Thanks and Regards, Siva. -Original Message- From: Chris Travers [mailto:chris.trav...@gmail.com] Sent: Wednesday, August 17, 2011 11:12 AM To: Siva Palanisamy Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to write a psql command inside a function? On Tue, Aug 16, 2011 at 10:33 PM, Siva Palanisamy wrote: > Hi All, > > > > I want to have a psql command '\COPY' inside a function. By default, this > command works as such. But, not inside a function. Please guide me. > I don't think that works I think you have to use SQL commands since, well, the function is running in the db, not in your client. Look up the SQL COPY command instead. Best Wishes, Chris Travers ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. --- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Postgresql as application server
On 08/16/2011 07:04 AM, Darren Duncan wrote: Chris Travers wrote: On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan wrote: I believe we basically have all the foundation already, with maybe procedures executable outside transactions being the last major part. Why is this desirable? Why is it more desirable than actually using the listen/notify infrastructure that exists already? Maybe listen/notify is sufficient by itself. I withdraw my "procedures executable outside transactions" comment for now, and just bring it up later if I can think of a specific use case that other mechanisms don't satisfy. -- Darren Duncan LISTEN/NOTIFY is good if you want to call an outside application, however, if you want to call another database function, then it is just plain wrong. I need a daemon that will listen to for notify calls so that it can open a new database session and call the function. With an asynchronous function, my function would be able to spawn a new session and run in its own transaction while the calling function would be able to complete and go away. See http://archives.postgresql.org/pgsql-hackers/2011-04/msg01503.php for my proposal on this issue. One problem we have with LISTEN/NOTIFY (and I haven't found the cause for this yet) is every once in a while my daemon stops listening. It may be after a month of use or longer, and may be caused by the database being restarted or something similar. When the daemon stops listening, it doesn't give any errors or indication that it isn't working anymore. It is only after a user complains that something hasn't been updated in a day or so that we manually restart it. It doesn't happen very often, but it does happen on occasion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
