Re: [HACKERS] List of binary-compatible data types
Noah, Also, JSON -- Text seems to be missing from the possible binary conversions. That's a TODO, I suppose. Only json -- text, not json -- text. Note that you can add the cast manually if you have an immediate need. Huh? Why would text -- JSON require a physical rewrite? We have to validate it, sure, but we don't need to rewrite it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
On Tue, Nov 05, 2013 at 10:00:15AM -0800, Josh Berkus wrote: Noah, Also, JSON -- Text seems to be missing from the possible binary conversions. That's a TODO, I suppose. Only json -- text, not json -- text. Note that you can add the cast manually if you have an immediate need. Huh? Why would text -- JSON require a physical rewrite? We have to validate it, sure, but we don't need to rewrite it. That's all true, but the system has no concept like this cast validates the data, never changing it. We would first need to add metadata supporting such a concept. On the other hand, create cast (json as text) without function; leans only on concepts the system already knows. -- Noah Misch EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
Noah, That's all true, but the system has no concept like this cast validates the data, never changing it. We would first need to add metadata supporting such a concept. On the other hand, create cast (json as text) without function; leans only on concepts the system already knows. Yeah, I'm thinking it might be worth coming up with a solution for that specific case. As users upgrade from 9.0 and 9.1 to 9.3, they're going to want to convert their text columns containing JSON to columns of the JSON type, and are going to be surprised how painful that is. Of course, if we get binary JSON in 9.4 (Oleg?), then a binary conversion will be required, so maybe it's a moot point. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
On 2013-11-05 11:15:29 -0800, Josh Berkus wrote: Noah, That's all true, but the system has no concept like this cast validates the data, never changing it. We would first need to add metadata supporting such a concept. On the other hand, create cast (json as text) without function; leans only on concepts the system already knows. Yeah, I'm thinking it might be worth coming up with a solution for that specific case. As users upgrade from 9.0 and 9.1 to 9.3, they're going to want to convert their text columns containing JSON to columns of the JSON type, and are going to be surprised how painful that is. There's zap chance of doing anything for 9.3, this would require quite a bit of code in tablecmds.c and that surely isn't going to happen in the backbranches. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
Andres, There's zap chance of doing anything for 9.3, this would require quite a bit of code in tablecmds.c and that surely isn't going to happen in the backbranches. Oh, sure, I was thinking of a workaround. Actually, being able to separate need to check contents from need to rewrite values could be useful for a lot of type conversions. I'd also love some way of doing a no-rewrite conversion between timestamp and timestamptz, based on the assumption that the original values are UTC time. That's one I encounter a lot. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] List of binary-compatible data types
Folks, From our docs: Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten. As an exception, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed ... Which is nice, but nowhere do we present users with a set of binary-compatible data types, even among the built-in types. I'd happily write this up, if I knew what the binary-compatible data types *were*. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
On 4 November 2013 21:58, Josh Berkus j...@agliodbs.com wrote: Folks, From our docs: Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten. As an exception, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed ... Which is nice, but nowhere do we present users with a set of binary-compatible data types, even among the built-in types. I'd happily write this up, if I knew what the binary-compatible data types *were*. You could try this: SELECT castsource::regtype::text, array_agg(casttarget::regtype order by casttarget::regtype::text) casttargets FROM pg_cast WHERE castmethod = 'b' GROUP BY 1 ORDER BY 1; -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
Thom, SELECT castsource::regtype::text, array_agg(casttarget::regtype order by casttarget::regtype::text) casttargets FROM pg_cast WHERE castmethod = 'b' GROUP BY 1 ORDER BY 1; Are we actually covering 100% of these for ALTER COLUMN now? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
On 11/04/2013 05:21 PM, Josh Berkus wrote: Thom, SELECT castsource::regtype::text, array_agg(casttarget::regtype order by casttarget::regtype::text) casttargets FROM pg_cast WHERE castmethod = 'b' GROUP BY 1 ORDER BY 1; Are we actually covering 100% of these for ALTER COLUMN now? Also, JSON -- Text seems to be missing from the possible binary conversions. That's a TODO, I suppose. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] List of binary-compatible data types
On Mon, Nov 04, 2013 at 05:23:36PM -0800, Josh Berkus wrote: On 11/04/2013 05:21 PM, Josh Berkus wrote: Thom, SELECT castsource::regtype::text, array_agg(casttarget::regtype order by casttarget::regtype::text) casttargets FROM pg_cast WHERE castmethod = 'b' GROUP BY 1 ORDER BY 1; Are we actually covering 100% of these for ALTER COLUMN now? Yes; ALTER TABLE ALTER TYPE refers to the same metadata as Thom's query. If you add to the list by issuing CREATE CAST ... WITHOUT FUNCTION, ALTER TABLE will respect that, too. Also, JSON -- Text seems to be missing from the possible binary conversions. That's a TODO, I suppose. Only json -- text, not json -- text. Note that you can add the cast manually if you have an immediate need. -- Noah Misch EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
