Re: Parameter - shared_memory_size
On Fri, 2025-01-10 at 12:14 +0530, Jayadevan M wrote: > I am trying to understand the parameter shared_memory_size. It is a static > parameter > that reports the size of the shared memory area. Does that imply that the > value may > change depending on the memory used by the server? No, that value will never change. It is set once after the start of the server, after the server has allocated shared buffers and other shared memory segments, and after the modules in "shared_preload_libraries" have had a chance to allocate shared memory too. Yours, Laurenz Albe
Re: Postgres do not support tinyint?
On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov wrote: > If you really need 1-byte integer, you can use "char" type. Cast it > to/from int. See comment at the end of the page > https://www.postgresql.org/docs/17/datatype-character.html Hi. What would be the best online doc to learn about the physical storage / format for rows/pages/btree, etc... To understand alignment, packing, varint or not, all those things. I'm quite familiar with the [SQLite format][1], having read that doc dozens of times, and I'd like a better low-level understanding for PostgreSQL as well. TIA, --DD [1]: https://www.sqlite.org/fileformat.html
Re: Display Bytea field
Andy Hartman wrote: > How thru a simple query can I make sure data matches and I can display it > > On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman wrote: > > > I have migrated over a Table from Mssql that had an Image column I now > > have it in Postgres Within psql, the bytea field can be copied into a large object with lo_from_bytea() [1], and then the large object exported into a local file with \lo_export [2] Alternatively, you could compare image checksums before and after moving them into postgres. The advantage is that you don't need to export or view any file, and you compare globally all your images. If the checksums are identical, the data are identical. On the MSSQL side, checksums can be computed with hashbytes() as suggested in this stackoverflow answer: [3] On the postgres side, use functions like md5() or sha256() directly on the bytea column. [1] https://www.postgresql.org/docs/current/lo-funcs.html [2] https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-LO-EXPORT [3] https://stackoverflow.com/a/33256990/ Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Display Bytea field
On Fri, Jan 10, 2025 at 7:49 AM Daniel Verite wrote: [snip] > Alternatively, you could compare image checksums before and > after moving them into postgres. The advantage is that you > don't need to export or view any file, and you compare globally > all your images. If the checksums are identical, the data are identical. > On the MSSQL side, checksums can be computed with hashbytes() > as suggested in this stackoverflow answer: [3] > On the postgres side, use functions like md5() or sha256() > directly on the bytea column. > This is what I did when migrating Oracle xLOB columns to bytea. Had to use upper(md5()). I didn't suggest this earlier, since I don't know the details of MSSQL's Image data type. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Postgres do not support tinyint?
Dominique Devienne писал(а) 2025-01-10 16:41: On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov wrote: If you really need 1-byte integer, you can use "char" type. Cast it to/from int. See comment at the end of the page https://www.postgresql.org/docs/17/datatype-character.html Hi. What would be the best online doc to learn about the physical storage / format for rows/pages/btree, etc... To understand alignment, packing, varint or not, all those things. I'm quite familiar with the [SQLite format][1], having read that doc dozens of times, and I'd like a better low-level understanding for PostgreSQL as well. TIA, --DD [1]: https://www.sqlite.org/fileformat.html I would recommend "PostgreSQL Internals" of Egor Rogov (Chapter 3 about tuples, other chapters also great) Free download from https://postgrespro.com/community/books/internals Also PostgreSQL source code is officially the part of the documentation. It is not joke. PostgreSQL source has clear comments in functions code and in structure declarations. You can compare with other sources, it is hard to find better comments made with love. Heap tuple information is in source file src/include/access/htup_details.h (HeapTupleHeaderData structure), but I recommend to read above book first, it simplier explains many internal concepts. -- Best regards, Vladlen Popolitov.
Re: Display Bytea field
On 2025-01-09 21:31 +0100, Andy Hartman wrote: > could it be done using Powershell? I use this: https://blog.cleverelephant.ca/2021/04/psql-binary.html But I don't know if that translates to PowerShell. -- Erik Wienhold signature.asc Description: PGP signature
Re: Postgres do not support tinyint?
Igor Korot писал(а) 2025-01-09 02:40: Hi, Christopphe, On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus wrote: > On Jan 8, 2025, at 11:30, Igor Korot wrote: > There is no boolean - it is 0-4 inclusive. Unless you have somehow gotten PostgreSQL running on an IBM 7070, the range 0-4 can be represented by three binary digits, aka booleans. :-) The only booleans I know of are 0 and 1. ;-) To be serious, though, the situation is: 1. If there are just one or two tinyints, having a tinyint type wouldn't save any space in the row. No it is not a lot of them. So then "smallint" is the best bet, right? Thank you 2. If there are a lot of them, it's worth encoding them into a bitstring. Hi! If you really need 1-byte integer, you can use "char" type. Cast it to/from int. See comment at the end of the page https://www.postgresql.org/docs/17/datatype-character.html -- Best regards, Vladlen Popolitov.
Re: Postgres do not support tinyint?
Dominique Devienne schrieb am 10.01.2025 um 10:41: > On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov > wrote: >> If you really need 1-byte integer, you can use "char" type. Cast it >> to/from int. See comment at the end of the page >> https://www.postgresql.org/docs/17/datatype-character.html > > Hi. What would be the best online doc to learn about the physical > storage / format for rows/pages/btree, etc... To understand alignment, > packing, varint or not, all those things. I'm quite familiar with the > [SQLite format][1], having read that doc dozens of times, and I'd like > a better low-level understanding for PostgreSQL as well. TIA, --DD The alignment requirement of each type is available in pg_type https://www.postgresql.org/docs/current/catalog-pg-type.html The physical layout on disk is described here: https://www.postgresql.org/docs/current/storage.html And the "Postgres intenrals" site might be helpful as well: https://www.interdb.jp/pg/pgsql01.html There are various (blog) posts on how to optimize space considering alignment * https://stackoverflow.com/a/7431468 * https://www.enterprisedb.com/blog/rocks-and-sand The "postgres_dba" toolset has a little SQL script to evaluate the space savings: https://github.com/NikolayS/postgres_dba/blob/master/sql/p1_alignment_padding.sql
