Re: Gradual migration from integer to bigint?
On Sun, 1 Oct 2023 at 14:37, Tom Lane wrote: > I think what you're asking for is a scheme whereby some rows in a > table have datatype X in a particular column while other rows in > the very same physical table have datatype Y in the same column. > That is not happening, because there'd be no way to tell which > case applies to any particular row. To be honest, I don't know enough about the postgresql on-disk format and tuple shape to be confident in how this would be solved. I was thinking more about the ergonomics of what would be helpful and wondering how viable it was. Sounds like not very viable. Rats. The docs [1] on changing column types include: > As an exception, when changing the type of an existing column, 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 ... and mention the specific case of switching between VARCHAR and TEXT not requiring a table or index rewrite. Seems like the specific case of int->bigint is impossible to make as easy, given the fixed sizes in the tuple and impossibility of knowing from tuple to tuple whether to read 4 or 8 bytes. regards, James [1] https://www.postgresql.org/docs/current/sql-altertable.html
What changes to a table cannot be detected by a trigger?
Triggers can be set for insert, update, delete, and truncate operations. Alter table operations, like adding or dropping a column from a table, do not cause an update trigger to fire but can be captured by an event trigger. Are there any situations where the data in a table can be changed and the change cannot be detected by a trigger or event trigger? Thanks, Phillip
Re: cache lookup failed for function 0
p...@pfortin.com writes: > I see the string "SET client_encoding = 'WIN1252';" in the dump -- some > files come from a remote colleague; but this has never been an issue > before... Hah! If I create the test database with encoding WIN1252 then your test fails as described. It *should* be complaining that it doesn't know how to convert from ISO-8859-1 to WIN1252, but BeginCopyFrom is neglecting to check for failure of FindDefaultConversionProc. Will fix that, thanks for the report. In the meantime, if use of WIN1252 was intentional then you'll need to find another way to transcode the data that was in that encoding. Probably, running your database in UTF8 is the best choice -- we can convert most anything to that. regards, tom lane
Re: Gradual migration from integer to bigint?
W dniu 30.09.2023 o 07:55, James Healy pisze: ... We shouldn't have let them get so big, but that's a conversation for another day. Some are approaching overflow and we're slowly doing the work to migrate to bigint. Mostly via the well understood "add a new id_bigint column, populate on new tuples, backfill the old, switch the PK" method. The backfill is slow on these large tables, but it works and there's plenty of blog posts and documentation to follow. wouldn't wrapping to negative numbers like: https://www.youtube.com/watch?v=XYRgTazYuZ4&t=1338s be a solution for you? At least for buying more time for the slow migration process. Or even as a definite solution if you now take care and not let the keys grow too quickly.
Re: Gradual migration from integer to bigint?
On Sat, Sep 30, 2023 at 11:37 PM Tom Lane wrote: > James Healy writes: > > However it doesn't really address the question of a gradual migration > > process that can read 32bit ints but insert/update as 64bit bigints. I > > remain curious about whether the postgres architecture just makes that > > implausible, or if it could be done and just hasn't because the > > options for a more manual migration are Good Enough. > > I think what you're asking for is a scheme whereby some rows in a > table have datatype X in a particular column while other rows in > the very same physical table have datatype Y in the same column. > That is not happening, because there'd be no way to tell which > case applies to any particular row. > Other databases do allow that sort of gradual migration. One example has an internal table of record descriptions indexed the table identifier and a description number. Each record includes a header with various useful bits including its description number. When reading a record, the system notes the description number and looks up the description before parsing the record into columns. The transition is made easier if the database indexes are generic - for example, numbers rather than decimal[12,6], int32, etc., and string rather than varchar[12]. That way, increasing a column size doesn't require re-indexing. But, those are decision that really had to be made early - making a major format change 25+ years in would break too much. Cheers, Ann > > > > >
Re: Gradual migration from integer to bigint?
On 10/1/23 12:04, Ireneusz Pluta wrote: W dniu 30.09.2023 o 07:55, James Healy pisze: ... We shouldn't have let them get so big, but that's a conversation for another day. Some are approaching overflow and we're slowly doing the work to migrate to bigint. Mostly via the well understood "add a new id_bigint column, populate on new tuples, backfill the old, switch the PK" method. The backfill is slow on these large tables, but it works and there's plenty of blog posts and documentation to follow. wouldn't wrapping to negative numbers like: https://www.youtube.com/watch?v=XYRgTazYuZ4&t=1338s be a solution for you? At least for buying more time for the slow migration process. Or even as a definite solution if you now take care and not let the keys grow too quickly. The application might not react well to negative numbers. -- Born in Arizona, moved to Babylonia.
