Re: [SQL] syncing - between databases
All on the same cluster (only one server). Although, it would be nice to have only one table there are real business reasons to dup the databases. I am interested in how you dealt with a queue table. Would you take a little time to describe the way it worked. Johnf On Saturday, May 12, 2012 08:53:52 PM you wrote: > Are these 5 databases on different servers and at different locations or > are they on the same local cluster? > If they are all on the same local cluster you may want to rethink how you > are storing customer data. The design you describe seems redundant. > > If you are dealing with multiple servers (and perhaps business rules that > require duplicate, writable user tables at each location?) then your plan > needs to account for network failure. A synchronous cross-network dblink > trigger mechanism left to its own devices will eventually fail and you will > be left with inconsistent data. Nothing wrong with dblink but you need to > build in some error handling. > > I've built systems that accomplished similar things by writing data to a > queue table (in addition to your local master customer table) which is then > reconciled/synced out to other nodes or process by an periodic script that > is able to deal with or alert on locking/dupe key/network and other errors > that keep it from properly syncing a row to all other nodes. This > introduces added durability to your sync mechanism but also introduces some > lag time. Pick your poison. > > -steve > > On Sat, May 12, 2012 at 7:28 AM, John Fabiani wrote: > > I need to maintain a sync-ed table across several databases. For > > example I have a customer table in 5 databases. If a user of any of > > the databases inserts a new customer I need to insert the new record > > into the other four databases. But question is updates and deletes. > > > > I can use a trigger and dblink to update the other databases when the > > action > > is an insert because in each of the other databases I don't have to > > worry > > about a locked record. But what happens if a user is updating at the > > same moment as a different user in a different database is updating the > > same customer. Can a race condition occur? > > > > I was thinking I could create a master database. And have all the other > > databases use dblink to excute the master trigger. > > > > > > Any advise would be helpful, > > > > Johnf > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On Fri, May 11, 2012 at 4:42 PM, Viktor Bojović wrote: > you can convert from type to type using ::varchar or ::char(size) or > ::integer > so you can use sequence but you will have to convert it's result to suitable > type (that can also be put in default value of user_id attribute) I'm not understanding why I'm not able to change this column type from char to integer? There are no non-numeric existing characters stored in this particular column (cust_id). I've gone so far as to delete the foreign key and primary key that associated with this column (cust_id) but still I get a generic error: forza=# \d customers Table "public.customers" Column| Type | Modifiers --++--- cust_id | character(10) | cust_name| character varying(100) | not null cust_address | character(50) | cust_city| character(50) | cust_state | character(5) | cust_zip | character(10) | cust_country | character(50) | cust_contact | character(50) | cust_email | character(255) | All the values in the column in question: forza=# SELECT cust_id forza-# FROM customers forza-# ORDER BY cust_id; cust_id 10001 10002 10003 10004 10005 (5 rows) forza=# ALTER TABLE customers ALTER COLUMN cust_id TYPE integer; ERROR: column "cust_id" cannot be cast to type integer When I view the logs in /var/log/postgresql.log, I see the same exact error printed above so I can only assume the problem is invalid SQL statement or I'm breaking some ANSI SQL rule. Can someone please help me understand how I can change the data type for this column? I've deleted the primary key constraint so I don't know if that was a good / bad idea. Thanks for any info / help! -Carlos -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
Carlos Mennens writes: > I'm not understanding why I'm not able to change this column type from > char to integer? > forza=# ALTER TABLE customers > ALTER COLUMN cust_id TYPE integer; > ERROR: column "cust_id" cannot be cast to type integer Try "ALTER ... cust_id TYPE integer USING cust_id::integer". If you don't specify a USING expression, the command requires an implicit coercion from one type to the other, and there is none from char(n) to int. You can force it with an explicit coercion, though. It strikes me that "cannot be cast" is a poor choice of words here, since the types *can* be cast if you try. Would it be better if the message said "cannot be cast implicitly to type foo"? We could also consider a HINT mentioning use of USING. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] syncing - between databases
Having all the dblink destinations on the same server makes it a much more viable option since the chances of the dblink update failing are greatly reduced. That said, here's a run down on the queue system I described with some specificity toward your situation. You can add a flag to each customer row that denotes whether or not the data has been synced to the other tables, ALTER TABLE customer ADD synced BOOLEAN DEFAULT FALSE; --watch out for how this default might affect existing customers and then have your sync process flip the flag to true when it has safely written the data to all other tables. Alternatively, you can store the data that needs to be sync'd in a separate table if making changes to the customer table isn't a viable option. CREATE TABLE customer_queue (LIKE customer); ALTER TABLE customer_queue ADD created_time TIMESTAMP DEFAULT now(); ALTER TABLE customer_queue ADD operation CHAR(1); -- 'i' = insert, 'u' == update, etc ALTER TABLE customer_queue ADD processed_time TIMESTAMP; ALTER TABLE customer_queue ADD processed BOOLEAN DEFAULT FALSE; ..or something similar If patching the application to write new/updated customer data to the customer_queue table (in addition to or instead of the customer table) is out of scope, you could populate it via trigger. Once you have a data structure that stores your customer data and the meta data which captures whether the row has been safely synced out to the other tables it's just a matter of writing a script that reads your sync meta data (queue table or your customer table where not processed) and processes all rows that are pending. SELECT foo,bar,baz FROM customer_queue WHERE NOT processed ORDER BY created_time; -- FIFO The script should be able to verify that a given row was safely written to all destinations before setting processed to true. Anyway, that's one of many ways to accomplish this and it's surely far from the best but I hope this is helpful. regards -steve On Sun, May 13, 2012 at 1:01 PM, John Fabiani wrote: > All on the same cluster (only one server). Although, it would be nice to > have only one table there are real business reasons to dup the databases. > > I am interested in how you dealt with a queue table. Would you take a > little > time to describe the way it worked. > > Johnf > > On Saturday, May 12, 2012 08:53:52 PM you wrote: > > Are these 5 databases on different servers and at different locations or > > are they on the same local cluster? > > If they are all on the same local cluster you may want to rethink how you > > are storing customer data. The design you describe seems redundant. > > > > If you are dealing with multiple servers (and perhaps business rules that > > require duplicate, writable user tables at each location?) then your plan > > needs to account for network failure. A synchronous cross-network dblink > > trigger mechanism left to its own devices will eventually fail and you > will > > be left with inconsistent data. Nothing wrong with dblink but you need > to > > build in some error handling. > > > > I've built systems that accomplished similar things by writing data to a > > queue table (in addition to your local master customer table) which is > then > > reconciled/synced out to other nodes or process by an periodic script > that > > is able to deal with or alert on locking/dupe key/network and other > errors > > that keep it from properly syncing a row to all other nodes. This > > introduces added durability to your sync mechanism but also introduces > some > > lag time. Pick your poison. > > > > -steve > > > > On Sat, May 12, 2012 at 7:28 AM, John Fabiani > wrote: > > > I need to maintain a sync-ed table across several databases. For > > > example I have a customer table in 5 databases. If a user of any of > > > the databases inserts a new customer I need to insert the new record > > > into the other four databases. But question is updates and deletes. > > > > > > I can use a trigger and dblink to update the other databases when the > > > action > > > is an insert because in each of the other databases I don't have to > > > worry > > > about a locked record. But what happens if a user is updating at the > > > same moment as a different user in a different database is updating the > > > same customer. Can a race condition occur? > > > > > > I was thinking I could create a master database. And have all the > other > > > databases use dblink to excute the master trigger. > > > > > > > > > Any advise would be helpful, > > > > > > Johnf > > > > > > -- > > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Finding Max Value in a Row
On Sun, May 13, 2012 at 8:11 PM, Tom Lane wrote: > It strikes me that "cannot be cast" is a poor choice of words here, > since the types *can* be cast if you try. Would it be better if the > message said "cannot be cast implicitly to type foo"? We could also > consider a HINT mentioning use of USING. > Without the hint, I don't think that there's a ton of value in changing the message as proposed. It's more accurate, so may be worth doing anyway, but probably won't be much more helpful than the current message to someone who hasn't encountered the problem before. If they've seen it before, the old message is likely sufficient to remind them.
